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 gem1gem install sqlite3Copied!You've have to have sqlite3 development libraries installed on your system1apt-get install libsqlite3-devCopied!
- 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 gem1gem install activerecordCopied!
MySQL database
- Install MySQL adapter gem1gem install mysqlCopied!
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 id1Attackers.find(1)Copied!
- by name1Attackers.find_by(name: "Anonymous")Copied!Result1#<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:
- 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 directoriesas 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 gem1gem install ruby-oci8 activerecord-oracle_enhanced-adapterCopied!
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!
Last modified 4yr ago
Copy link