Databases

    • Install sqlite3 gem

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

    • 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
    • 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
    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.
    18. mysql> grant all on rubyfu_db.* to 'root'@'localhost';
    19. Query OK, 0 rows affected (0.00 sec)
    21. mysql> use rubyfu_db;
    22. Database changed
    23. mysql> create table attackers (
    24.     ->   id int not null auto_increment,
    25.     ->   name varchar(100) not null,
    26.     ->   ip text not null,
    27.     ->   primary key (id)
    28.     -> );
    29. Query OK, 0 rows affected (0.01 sec)
    31. 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)

    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
    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
    27. attacker.id
    28. attacker.ip
    30. # Delete a table Item
    31. Attackers.find(2).destroy
    • Prerequisites

    in order to make -which is the main dependency for oracle driver- works you’ve to do some extra steps:

    • Download links for Linux | | 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

    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
    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
    • Install MSSQL adapter gem