Databases
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
    1
    gem install sqlite3
    Copied!
    You've have to have sqlite3 development libraries installed on your system
    1
    apt-get install libsqlite3-dev
    Copied!
    Basic operations
1
require "sqlite3"
2
3
# Open/Create a database
4
db = SQLite3::Database.new "rubyfu.db"
5
6
# Create a table
7
rows = db.execute <<-SQL
8
CREATE TABLE attackers (
9
id INTEGER PRIMARY KEY AUTOINCREMENT,
10
name TEXT NOT NULL,
11
ip CHAR(50)
12
);
13
SQL
14
15
# Execute a few inserts
16
{
17
'Anonymous' => "192.168.0.7",
18
'LulzSec' => "192.168.0.14",
19
'Lizard Squad' => "192.168.0.253"
20
}.each do |attacker, ip|
21
db.execute("INSERT INTO attackers (name, ip)
22
VALUES (?, ?)", [attacker, ip])
23
end
24
25
# Find a few rows
26
db.execute "SELECT id,name,ip FROM attackers"
27
28
# List all tables
29
db.execute "SELECT * FROM sqlite_master where type='table'"
Copied!

Active Record

    Install ActiveRecord gem
    1
    gem install activerecord
    Copied!

MySQL database

    Install MySQL adapter gem
    1
    gem install mysql
    Copied!
Login to mysql console and create database rubyfu_db and table attackers
1
create database rubyfu_db;
2
3
grant all on rubyfu_db.* to 'root'@'localhost';
4
5
create table attackers (
6
id int not null auto_increment,
7
name varchar(100) not null,
8
ip text not null,
9
primary key (id)
10
);
11
12
exit
Copied!
The outputs look like following
1
mysql -u root -p
2
Enter password:
3
Welcome to the MySQL monitor. Commands end with ; or \g.
4
Your MySQL connection id is 41
5
Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu)
6
7
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
8
9
Oracle is a registered trademark of Oracle Corporation and/or its
10
affiliates. Other names may be trademarks of their respective
11
owners.
12
13
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15
16
mysql> create database rubyfu_db;
17
Query OK, 1 row affected (0.00 sec)
18
19
mysql> grant all on rubyfu_db.* to 'root'@'localhost';
20
Query OK, 0 rows affected (0.00 sec)
21
22
mysql> use rubyfu_db;
23
Database changed
24
mysql> create table attackers (
25
-> id int not null auto_increment,
26
-> name varchar(100) not null,
27
-> ip text not null,
28
-> primary key (id)
29
-> );
30
Query OK, 0 rows affected (0.01 sec)
31
32
mysql> exit
Copied!
Now, let's to connect to rubyfu_db database
1
require 'active_record'
2
ActiveRecord::Base.establish_connection(
3
:adapter => "mysql",
4
:username => "root",
5
:password => "root",
6
:host => "localhost",
7
:database => "rubyfu_db"
8
)
9
10
class Attackers < ActiveRecord::Base
11
end
Copied!
    Using the ActiveRecord library, available as the activerecord gem.
    Using the ActiveRecord adapter namely mysql
    Establishing a connection to the database rubyfu_db
    Creating a class called Attackers following the conventions mentioned above (attacker)
1
Attackers.create(:name => 'Anonymous', :ip => "192.168.0.7")
2
Attackers.create(:name => 'LulzSec', :ip => "192.168.0.14")
3
Attackers.create(:name => 'Lizard Squad', :ip => "192.168.0.253")
Copied!
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.
You can find the item
    by id
    1
    Attackers.find(1)
    Copied!
    by name
    1
    Attackers.find_by(name: "Anonymous")
    Copied!
    Result
    1
    #<Attackers:0x000000010a6ad0 id: 1, name: "Anonymous", ip: "192.168.0.7">
    Copied!
or you can work it as object
1
attacker = Attackers.find(3)
2
attacker.id
3
attacker.name
4
attacker.ip
Copied!
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:
1
Attackers.find(2).destroy
Copied!
So to write a complete script,
1
#!/usr/bin/env ruby
2
# KING SABRI | @KINGSABRI
3
# ActiveRecord with MySQL
4
#
5
require 'active_record'
6
7
# Connect to database
8
ActiveRecord::Base.establish_connection(
9
:adapter => "mysql",
10
:username => "root",
11
:password => "root",
12
:host => "localhost",
13
:database => "rubyfu_db"
14
)
15
16
# Create Active Record Model for the table
17
class Attackers < ActiveRecord::Base
18
end
19
20
# Create New Entries to the table
21
Attackers.create(:name => 'Anonymous', :ip => "192.168.0.7")
22
Attackers.create(:name => 'LulzSec', :ip => "192.168.0.14")
23
Attackers.create(:name => 'Lizard Squad', :ip => "192.168.0.253")
24
25
# Interact with table items
26
attacker = Attackers.find(3)
27
attacker.id
28
attacker.name
29
attacker.ip
30
31
# Delete a table Item
32
Attackers.find(2).destroy
Copied!

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:
    Download links for Linux | Windows | Mac
      instantclient-basic-[OS].[Arch]-[VERSION].zip
      instantclient-sqlplus-[OS].[Arch]-[VERSION].zip
      instantclient-sdk-[OS].[Arch]-[VERSION].zip
    Unzip downloaded files
1
unzip -qq instantclient-basic-linux.x64-12.1.0.2.0.zip
2
unzip -qq instantclient-sdk-linux.x64-12.1.0.2.0.zip
3
unzip -qq instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
Copied!
    Create system directories
    as root / sudo
1
mkdir -p /usr/local/oracle/{network,product/instantclient_64/12.1.0.2.0/{bin,lib,jdbc/lib,rdbms/jlib,sqlplus/admin/}}
Copied!
The file structure should be
1
/usr/local/oracle/
2
├── admin
3
│ └── network
4
└── product
5
└── instantclient_64
6
└── 12.1.0.2.0
7
├── bin
8
├── jdbc
9
│ └── lib
10
├── lib
11
├── rdbms
12
│ └── jlib
13
└── sqlplus
14
└── admin
Copied!
    Move files
1
cd instantclient_12_1
2
3
mv ojdbc* /usr/local/oracle/product/instantclient_64/12.1.0.2.0/jdbc/lib/
4
mv x*.jar /usr/local/oracle/product/instantclient_64/12.1.0.2.0/rdbms/jlib/
5
# rename glogin.sql to login.sql
6
mv glogin.sql /usr/local/oracle/product/instantclient_64/12.1.0.2.0/sqlplus/admin/login.sql
7
mv sdk /usr/local/oracle/product/instantclient_64/12.1.0.2.0/lib/
8
mv *README /usr/local/oracle/product/instantclient_64/12.1.0.2.0/
9
mv * /usr/local/oracle/product/instantclient_64/12.1.0.2.0/bin/
10
# Symlink of instantclient
11
cd /usr/local/oracle/product/instantclient_64/12.1.0.2.0/bin
12
ln -s libclntsh.so.12.1 libclntsh.so
13
ln -s ../lib/sdk sdk
14
cd -
Copied!
    Setup environment
Append oracle environment variables in to ~/.bashrc Then add the following:
1
# Oracle Environment
2
export ORACLE_BASE=/usr/local/oracle
3
export ORACLE_HOME=$ORACLE_BASE/product/instantclient_64/12.1.0.2.0
4
export PATH=$ORACLE_HOME/bin:$PATH
5
LD_LIBRARY_PATH=$ORACLE_HOME/bin
6
export LD_LIBRARY_PATH
7
export TNS_ADMIN=$ORACLE_BASE/admin/network
8
export SQLPATH=$ORACLE_HOME/sqlplus/admin
Copied!
Then run:
1
source ~/.bashrc
Copied!
    Install Oracle adapter gem
    1
    gem install ruby-oci8 activerecord-oracle_enhanced-adapter
    Copied!
Now let's to connect
1
require 'active_record'
2
3
ActiveRecord::Base.establish_connection(
4
:adapter => "oracle_enhanced",
5
:database => "192.168.0.13:1521/XE",
6
:username => "SYSDBA",
7
:password => "welcome1"
8
)
9
10
class DBAUsers < ActiveRecord::Base
11
end
Copied!

MSSQL database

    Install MSSQL adapter gem
1
gem install tiny_tds activerecord-sqlserver-adapter
Copied!