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

    You’ve have to have sqlite3 development libraries installed on your system

    1. apt-get install libsqlite3-dev

  • Basic operations

  1. require "sqlite3"
  3. # Open/Create a database
  4. db = SQLite3::Database.new "rubyfu.db"
  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
  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
  25. # Find a few rows
  26. db.execute "SELECT id,name,ip FROM attackers"
  28. # List all tables
  29. db.execute  "SELECT * FROM sqlite_master where type='table'"

Active Record

  • Install ActiveRecord gem
    1. gem install activerecord

MySQL database

  • Install MySQL adapter gem
    1. gem install mysql

Login to mysql console and create database rubyfu_db and table attackers

  1. create database rubyfu_db;
  3. grant all on rubyfu_db.* to 'root'@'localhost';
  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. );
  12. exit

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)
  7. Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  16. mysql> create database rubyfu_db;
  17. Query OK, 1 row affected (0.00 sec)
  19. mysql> grant all on rubyfu_db.* to 'root'@'localhost';
  20. Query OK, 0 rows affected (0.00 sec)
  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)
  32. mysql> exit

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. )
  10. class Attackers < ActiveRecord::Base
  11. end
  • 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")

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)
  • by name
    1. Attackers.find_by(name: "Anonymous")
    Result
    1. #<Attackers:0x000000010a6ad0 id: 1, name: "Anonymous", ip: "192.168.0.7">

or you can work it as object

  1. attacker = Attackers.find(3)
  2. attacker.id
  3. attacker.name
  4. attacker.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:

  1. Attackers.find(2).destroy

So to write a complete script,

  1. #!/usr/bin/env ruby
  2. # KING SABRI | @KINGSABRI
  3. # ActiveRecord with MySQL
  4. #
  5. require 'active_record'
  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.                                        )
  16. # Create Active Record Model for the table
  17. class Attackers < ActiveRecord::Base
  18. end
  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")
  25. # Interact with table items
  26. attacker = Attackers.find(3)
  27. attacker.id
  28. attacker.name
  29. attacker.ip
  31. # Delete a table Item
  32. Attackers.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:

  • 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
  • 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/}}

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
  • Move files
  1. cd instantclient_12_1
  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 -
  • 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

Then run:

  1. source ~/.bashrc
  • Install Oracle adapter gem
    1. gem install ruby-oci8 activerecord-oracle_enhanced-adapter

Now let’s to connect

  1. require 'active_record'
  3. ActiveRecord::Base.establish_connection(
  4.                       :adapter  => "oracle_enhanced",
  5.                       :database => "192.168.0.13:1521/XE",
  6.                       :username => "SYSDBA",
  7.                       :password => "welcome1"
  8.                        )
  10. class DBAUsers < ActiveRecord::Base
  11. end

MSSQL database

  • Install MSSQL adapter gem
  1. gem install tiny_tds activerecord-sqlserver-adapter