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.
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 <<-SQLCREATE TABLE attackers (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT 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'"
Install ActiveRecord gem
gem install activerecord
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 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 41Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 changedmysql> 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
Now, let's to connect to rubyfu_db database
require 'active_record'ActiveRecord::Base.establish_connection(:adapter => "mysql",:username => "root",:password => "root",:host => "localhost",:database => "rubyfu_db")class Attackers < ActiveRecord::Baseend
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)
Attackers.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")
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
Attackers.find(1)
by name
Attackers.find_by(name: "Anonymous")
Result
#<Attackers:0x000000010a6ad0 id: 1, name: "Anonymous", ip: "192.168.0.7">
or you can work it as object
attacker = Attackers.find(3)attacker.idattacker.nameattacker.ip
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 tableclass Attackers < 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
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
unzip -qq instantclient-basic-linux.x64-12.1.0.2.0.zipunzip -qq instantclient-sdk-linux.x64-12.1.0.2.0.zipunzip -qq instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
Create system directories
as root / sudo
mkdir -p /usr/local/oracle/{network,product/instantclient_64/12.1.0.2.0/{bin,lib,jdbc/lib,rdbms/jlib,sqlplus/admin/}}
The file structure should be
/usr/local/oracle/├── admin│ └── network└── product└── instantclient_64└── 12.1.0.2.0├── bin├── jdbc│ └── lib├── lib├── rdbms│ └── jlib└── sqlplus└── admin
Move files
cd instantclient_12_1mv ojdbc* /usr/local/oracle/product/instantclient_64/12.1.0.2.0/jdbc/lib/mv x*.jar /usr/local/oracle/product/instantclient_64/12.1.0.2.0/rdbms/jlib/# rename glogin.sql to login.sqlmv glogin.sql /usr/local/oracle/product/instantclient_64/12.1.0.2.0/sqlplus/admin/login.sqlmv sdk /usr/local/oracle/product/instantclient_64/12.1.0.2.0/lib/mv *README /usr/local/oracle/product/instantclient_64/12.1.0.2.0/mv * /usr/local/oracle/product/instantclient_64/12.1.0.2.0/bin/# Symlink of instantclientcd /usr/local/oracle/product/instantclient_64/12.1.0.2.0/binln -s libclntsh.so.12.1 libclntsh.soln -s ../lib/sdk sdkcd -
Setup environment
Append oracle environment variables in to ~/.bashrc
Then add the following:
# Oracle Environmentexport ORACLE_BASE=/usr/local/oracleexport ORACLE_HOME=$ORACLE_BASE/product/instantclient_64/12.1.0.2.0export PATH=$ORACLE_HOME/bin:$PATHLD_LIBRARY_PATH=$ORACLE_HOME/binexport LD_LIBRARY_PATHexport TNS_ADMIN=$ORACLE_BASE/admin/networkexport SQLPATH=$ORACLE_HOME/sqlplus/admin
Then run:
source ~/.bashrc
Install Oracle adapter gem
gem install ruby-oci8 activerecord-oracle_enhanced-adapter
Now let's to connect
require 'active_record'ActiveRecord::Base.establish_connection(:adapter => "oracle_enhanced",:database => "192.168.0.13:1521/XE",:username => "SYSDBA",:password => "welcome1")class DBAUsers < ActiveRecord::Baseend
Install MSSQL adapter gem
gem install tiny_tds activerecord-sqlserver-adapter