Databases

Twitter Github NEW! Black Hat Ruby Buy Me a Coffee
Search…
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!
SQL Injection Scanner
Extending Burp Suite
Copy link
Contents