Dealing with database is a required knowledge in web testing and here we will go though most known databases and how to deal with it in ruby.
SQLite
Install sqlite3 gem
gem install sqlite3
You've have to have sqlite3 development libraries installed on your system
apt-get install libsqlite3-dev
Basic operations
require"sqlite3"# Open/Create a databasedb =SQLite3::Database.new"rubyfu.db"# Create a tablerows = db.execute <<-SQLCREATETABLEattackers ( id INTEGERPRIMARY KEY AUTOINCREMENT,nameTEXTNOT NULL, ip CHAR(50));SQL# Execute a few inserts{'Anonymous'=>"192.168.0.7",'LulzSec'=>"192.168.0.14",'Lizard Squad'=>"192.168.0.253"}.each do|attacker, ip| db.execute("INSERT INTO attackers (name, ip) VALUES (?, ?)", [attacker, ip])end# Find a few rowsdb.execute "SELECT id,name,ip FROM attackers"# List all tablesdb.execute "SELECT * FROM sqlite_master where type='table'"
Active Record
Install ActiveRecord gem
gem install activerecord
MySQL database
Install MySQL adapter gem
gem install mysql
Login to mysql console and create database rubyfu_db and table attackers
create database rubyfu_db;
grant all on rubyfu_db.* to 'root'@'localhost';
create table attackers (
id int not null auto_increment,
name varchar(100) not null,
ip text not null,
primary key (id)
);
exit
The outputs look like following
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database rubyfu_db;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on rubyfu_db.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> use rubyfu_db;
Database changed
mysql> create table attackers (
-> id int not null auto_increment,
-> name varchar(100) not null,
-> ip text not null,
-> primary key (id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> exit
You will observe that ActiveRecord examines the database tables themselves to find out which columns are available. This is how we were able to use accessor methods for participant.name without explicitly defining them: we defined them in the database, and ActiveRecord picked them up.
If you want to delete an item from the database, you can use the destroy (Deletes the record in the database) method of ActiveRecord::Base:
Attackers.find(2).destroy
So to write a complete script,
#!/usr/bin/env ruby# KING SABRI | @KINGSABRI# ActiveRecord with MySQL#require'active_record'# Connect to databaseActiveRecord::Base.establish_connection( :adapter =>"mysql", :username =>"root", :password =>"root", :host =>"localhost", :database =>"rubyfu_db" )# Create Active Record Model for the tableclassAttackers<ActiveRecord::Baseend# Create New Entries to the tableAttackers.create(:name =>'Anonymous', :ip =>"192.168.0.7")Attackers.create(:name =>'LulzSec', :ip =>"192.168.0.14")Attackers.create(:name =>'Lizard Squad', :ip =>"192.168.0.253")# Interact with table itemsattacker =Attackers.find(3)attacker.idattacker.nameattacker.ip# Delete a table ItemAttackers.find(2).destroy
Oracle database
Prerequisites
in order to make ruby-oci8 -which is the main dependency for oracle driver- works you've to do some extra steps: