SQL Injection (SQLi)
Overview
SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted
into an entry field for execution (e.g. to dump the database contents to the
attacker). SQL injection must exploit asecurity vulnerability in an application's software, for example, when user input is
either incorrectly filtered for string literalescape characters embedded in SQL statements or user input is not strongly
typed and unexpectedly
executed. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.
SQL injection attacks allow attackers to spoof identity, tamper
with existing data, cause repudiation issues such as voiding transactions or
changing balances, allow the complete disclosure of all data on the system,
destroy the data or make it otherwise unavailable, and become administrators of
the database server.
In a 2012 study, it was observed that the average web
application received 4 attack campaigns per month, and retailers received twice
as many attacks as other industries.
SQL injection (SQLI) is considered one of the top 10 web
application vulnerabilities of 2007 and 2010 by the Open Web Application Security Project.
In 2013, SQLI was rated the number one attack on the OWASP top ten.
There are four main sub-classes of SQL injection:
·
Classic SQLI
·
Blind or Inference SQL injection
·
Database management system-specific SQLI
·
Compounded SQLI
·
SQL injection + insufficient
authentication
·
SQL injection + DDoS attacks
·
SQL injection + DNS hijacking
·
SQL injection + XSS
The Storm Worm is
one representation of Compounded SQLI.
Technical
implementations
Incorrectly filtered escape characters
This form of SQL injection occurs
when user input is not filtered for escape
characters and is then passed into an SQL statement. This
results in the potential manipulation of the statements performed on the
database by the end-user of the application.
The following line of code
illustrates this vulnerability:
statement =
"SELECT * FROM users WHERE name = '" + userName + "';"
This SQL code is designed to pull up
the records of the specified username from its table of users. However, if the
"userName" variable is crafted in a specific way by a malicious user,
the SQL statement may do more than the code author intended. For example,
setting the "userName" variable as:
' OR '1'='1
or using comments to even block the
rest of the query (there are three types of SQL comments).
All three lines have a space at the end:
' OR '1'='1'
--
' OR '1'='1'
({
' OR '1'='1'
/*
renders one of the following SQL
statements by the parent language:
SELECT * FROM users WHERE name = '' OR '1'='1';
SELECT * FROM users WHERE name = '' OR '1'='1' -- ';
If this code were to be used in an
authentication procedure then this example could be used to force the selection
of every data field (*) from all users rather than from one
specific user name as the coder intended, because the evaluation of '1'='1' is
always true
The following value of "userName" in the statement
below would cause the deletion of the "users" table as well as the
selection of all data from the "userinfo" table (in essence revealing
the information of every user), using an API that allows multiple
statements:
a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't
This input renders the final SQL statement as follows and
specified:
SELECT * FROM users WHERE name = 'a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't';
While most SQL server implementations allow multiple statements
to be executed with one call in this way, some SQL APIs such as PHP's
mysql_query() function do not allow this for security reasons. This prevents
attackers from injecting entirely separate queries, but doesn't stop them from
modifying queries.
This would result in the following SQL query being run against
the database server.
SELECT id FROM users WHERE username=’username’ AND password=’password’ OR 1=1’
An attacker can also comment out the rest of the SQL statement
to control the execution of the SQL query further.
-- MySQL, MSSQL, Oracle, PostgreSQL, SQLite' OR '1'='1' --' OR '1'='1' /*-- MySQL' OR '1'='1' #-- Access (using null characters)' OR '1'='1' ' OR '1'='1' %16
Incorrect type handling
This form of SQL injection occurs when a user-supplied field is not strongly typed or is not checked for type constraints. This could
take place when a numeric field is to be used in a SQL statement, but the
programmer makes no checks to validate that the user supplied input is numeric.
For example:
statement := "SELECT * FROM userinfo WHERE id =" + a_variable + ";"
It is clear from this statement that the author intended
a_variable to be a number correlating to the "id" field. However, if
it is in fact a string then the end-user may manipulate the statement as they choose, thereby bypassing
the need for escape characters. For example, setting a_variable to
1;DROP TABLE users
will drop (delete) the "users" table from the
database, since the SQL becomes:
SELECT * FROM userinfo WHERE id=1; DROP TABLE users;
Blind SQL injection
Blind SQL Injection is used when a web application is
vulnerable to an SQL injection but the results of the injection are not visible
to the attacker. The page with the vulnerability may not be one that displays
data but will display differently depending on the results of a logical
statement injected into the legitimate SQL statement called for that page. This
type of attack can become time-intensive because a new statement must be
crafted for each bit recovered. There are several tools that can automate these
attacks once the location of the vulnerability and the target information has
been established.
Conditional
responses
One type of blind SQL injection forces the database to evaluate
a logical statement on an ordinary application screen. As an example, a book
review website uses aquery string to determine which book review to
display. So the URL
http://books.example.com/showReview.php?ID=5 would cause the server to
run the querySELECT * FROM bookreviews WHERE ID = 'Value(ID)';
from which it would populate the review page with data from the
review with ID 5,
stored in the table bookreviews.
The query happens completely on the server; the user does not know the names of
the database, table, or fields, nor does the user know the query string. The
user only sees that the above URL returns a book review.
A hacker can load the URLs http://books.example.com/showReview.php?ID=5 OR 1=1 and http://books.example.com/showReview.php?ID=5 AND 1=2,
which may result in queries respectively.
SELECT * FROM bookreviews WHERE ID = '5' OR '1'='1';
SELECT * FROM bookreviews WHERE ID = '5' AND '1'='2';
If the original review loads with the "1=1" URL and a
blank or error page is returned from the "1=2" URL, and the returned
page has not been created to alert the user the input is invalid, or in other
words, has been caught by an input test script, the site is likely vulnerable
to a SQL injection attack as the query will likely have passed through
successfully in both cases.
The hacker may proceed with this query string designed to reveal
the version number of MySQL running on the server: http://books.example.com/showReview.php?ID=5 AND substring(@@version, 1, INSTR(@@version, '.') - 1)=4,
which would show the book
review on a server running MySQL 4 and a blank or error page otherwise.
The hacker can continue to use code within query strings to
glean more information from the server until another avenue of attack is
discovered or his or her goals are achieved.
Second order SQL injection
Second order SQL injection occurs when submitted values contain
malicious commands that are stored rather than executed immediately. In some
cases, the application may correctly encode an SQL statement and store it as
valid SQL. Then, another part of that application without controls to protect
against SQL injection might execute that stored SQL statement. This attack
requires more knowledge of how submitted values are later used. Automated web
application security scanners would not easily detect this type of SQL
injection and may need to be manually instructed where to check for evidence
that it is being attempted.
What’s the
worst an attacker can do with SQL?
SQL is a programming language designed for managing data stored
in an RDBMS, therefore SQL can be used to access, modify and delete data.
Furthermore, in specific cases, an RDBMS could also run commands on the
operating system from an SQL statement.
Keeping the above in
mind, when considering the following, it’s easier to understand how lucrative a
successful SQL injection attack can be for an attacker.
- An attacker can use SQL injection
to bypass authentication or even impersonate specific users.
- One of SQL’s primary functions is
to select data based on a query and output the result of that query. An
SQL injection vulnerability could allow the complete disclosure of data
residing on a database server.
- Since web applications use SQL to
alter data within a database, an attacker could use SQL injection to alter
data stored in a database. Altering data affects data integrity and could
cause repudiation issues, for instance, issues such as voiding
transactions, altering balances and other records.
- SQL is used to delete records from
a database. An attacker could use an SQL injection vulnerability to delete
data from a database. Even if an appropriate backup strategy is employed,
deletion of data could affect an application’s availability until the
database is restored.
- Some database servers are
configured (intentional or otherwise) to allow arbitrary execution of
operating system commands on the database server. Given the right
conditions, an attacker could use SQL injection as the initial vector in
an attack of an internal network that sits behind a firewall.
A basic demonstration
of SQL injection
SQL Injection Based on 1=1 is Always
True
SELECT * FROM Users WHERE UserId = 105 or
1=1
SQL Injection Based
on ""="" is Always True
SELECT * FROM Users WHERE Name
="" or ""="" AND Pass ="" or
""=""
SQL Injection Based on Batched SQL
Statements
SELECT * FROM Users; DROP TABLE Suppliers
Server Code
txtUserId =
getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
SELECT * FROM Users WHERE UserId = 105;
DROP TABLE Suppliers
The SQL query below gets
the password of an input user from the USERS table.
SELECT USERNAME, PASSWORD FROM USERS WHERE USERNAME = 'CLUB'
Developers are ignorant
that their above query can be misused to an extent that it can list login names
and passwords, which is relevant and crucial information for an organization.
An invader can give input as
''OR 1=1
which can retrieve all
login and password.
SELECT USERNAME, PASSWORD FROM USERS WHERE USERNAME = '' OR 1=1
In the above SQL, WHERE
clause has two filter conditions. First condition yields FALSE while the second
condition gives TRUE output. Since both are combined using OR logical operator,
the combination gives TRUE and query retrieves all the login names and their
passwords.
Like the above technique, SQL code can be exploited in multiple ways. Dynamic SQL and User/Definer right preserved subprograms are most prone to SQL injections.
Like the above technique, SQL code can be exploited in multiple ways. Dynamic SQL and User/Definer right preserved subprograms are most prone to SQL injections.
SQL Injection: Example
A procedure P_GET_SAL was created to get the salary of input Employee Id.
A procedure P_GET_SAL was created to get the salary of input Employee Id.
CREATE OR REPLACE PROCEDURE P_GET_SAL (P_ENAME VARCHAR2 DEFAULT NULL)
AS
CUR SYS_REFCURSOR;
V_ENAME VARCHAR2(100);
V_SAL NUMBER;
BEGIN
V_STMT := 'SELECT ENAME, SALARY FROM EMPLOYEE WHERE ENAME = '''|| P_ENAME || '''';
DBMS_OUTPUT.PUT_LINE(V_STMT);
OPEN CUR FOR V_STMT;
LOOP
FETCH CUR INTO V_ENAME, V_SAL;
EXIT WHEN CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee : '||V_ENAME||' draws '||TO_CHAR(V_SAL));
END LOOP;
CLOSE CUR;
END;
AS
CUR SYS_REFCURSOR;
V_ENAME VARCHAR2(100);
V_SAL NUMBER;
BEGIN
V_STMT := 'SELECT ENAME, SALARY FROM EMPLOYEE WHERE ENAME = '''|| P_ENAME || '''';
DBMS_OUTPUT.PUT_LINE(V_STMT);
OPEN CUR FOR V_STMT;
LOOP
FETCH CUR INTO V_ENAME, V_SAL;
EXIT WHEN CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee : '||V_ENAME||' draws '||TO_CHAR(V_SAL));
END LOOP;
CLOSE CUR;
END;
A malicious input can be
given in below ways to inject the SQL. Illustration is as below.
SQL> EXEC P_GET_SAL(‘KING’);
Employee KING draws 4500
PL/SQL PROCEDURE successfully completed.
PL/SQL PROCEDURE successfully completed.
SQL> EXEC P_GET_SAL('KING'' UNION SELECT ENAME,
SALARY FROM EMPLOYEE WHERE 1=1');
Employee KING draws 4500
Employee ALLEN draws 1200
Employee MIKE draws 3400
Employee KATE draws 2300
Employee PAUL draws 6400
Employee TOMY draws 2700
Employee JENNY draws 6200
Employee JAKES draws 4600
PL/SQL PROCEDURE successfully completed.
Employee ALLEN draws 1200
Employee MIKE draws 3400
Employee KATE draws 2300
Employee PAUL draws 6400
Employee TOMY draws 2700
Employee JENNY draws 6200
Employee JAKES draws 4600
PL/SQL PROCEDURE successfully completed.
Strategies
to Resolve SQL Injection
Several strategies can be adopted to safeguard the SQL code and eradicate the impacts of SQL injection in applications. Some of them are listed below.
1. Use of Static SQL
2. Using Invoker’s rights
3. Use of Dynamic SQL with bind arguments
4. Validate and sanitize input using DBMS_ASSERT
Several strategies can be adopted to safeguard the SQL code and eradicate the impacts of SQL injection in applications. Some of them are listed below.
1. Use of Static SQL
2. Using Invoker’s rights
3. Use of Dynamic SQL with bind arguments
4. Validate and sanitize input using DBMS_ASSERT
1) Using Static SQL
Static SQL reduces the chances of SQL injection as dynamic statements are more prone to malicious inputs and code exploitations.
Static SQL reduces the chances of SQL injection as dynamic statements are more prone to malicious inputs and code exploitations.
We already saw the attack on P_GET_SAL in earlier illustrations. The dynamic SQL used in the procedure can be converted into static as below to ignore the bad inputs.
Code (SQL):
CREATE OR REPLACE PROCEDURE P_GET_SAL (P_ENAME VARCHAR2 DEFAULT NULL)
AS
BEGIN
FOR I IN
(SELECT ENAME, SALARY
FROM EMPLOYEE
WHERE ENAME= P_ENAME)
LOOP
DBMS_OUTPUT.PUT_LINE('Employee : '||I.ENAME||' draws '||TO_CHAR(I.SALARY));
END LOOP;
END;
/
SQL> EXECUTE P_GET_SAL('KING');
Employee KING draws 4500
PL/SQL PROCEDURE successfully completed.
SQL> EXEC P_GET_SAL('KING'' UNION SELECT ENAME, SALARY FROM EMPLOYEE WHERE 1=1');
PL/SQL PROCEDURE successfully completed.
AS
BEGIN
FOR I IN
(SELECT ENAME, SALARY
FROM EMPLOYEE
WHERE ENAME= P_ENAME)
LOOP
DBMS_OUTPUT.PUT_LINE('Employee : '||I.ENAME||' draws '||TO_CHAR(I.SALARY));
END LOOP;
END;
/
SQL> EXECUTE P_GET_SAL('KING');
Employee KING draws 4500
PL/SQL PROCEDURE successfully completed.
SQL> EXEC P_GET_SAL('KING'' UNION SELECT ENAME, SALARY FROM EMPLOYEE WHERE 1=1');
PL/SQL PROCEDURE successfully completed.
2) Using invoker’s rights to reduce SQL Injection
A subprogram can be set to be executed by its Invoker’s rights. By default, it executes by its definer’s access rights. If a subprogram is marked as AUTHID CURRENT_USER, Oracle server validates the rights of the current user to execute the subprogram or not.
The below procedure P_ACTION is created to change the password of an input username. Note that it is a system related activity, so its access must be for limited users.
CREATE OR REPLACE PROCEDURE P_ACTION(P_USER VARCHAR2 DEFAULT NULL,
P_NEWPASS VARCHAR2 DEFAULT NULL)
AUTHID CURRENT_USER
IS
V_STMT VARCHAR2(500);
BEGIN
V_STMT := 'ALTER USER '||P_USER ||' IDENTIFIED BY '||P_NEWPASS;
EXECUTE IMMEDIATE V_STMT;
END P_ACTION;
P_NEWPASS VARCHAR2 DEFAULT NULL)
AUTHID CURRENT_USER
IS
V_STMT VARCHAR2(500);
BEGIN
V_STMT := 'ALTER USER '||P_USER ||' IDENTIFIED BY '||P_NEWPASS;
EXECUTE IMMEDIATE V_STMT;
END P_ACTION;
The procedure P_ACTION is
owned by SYS. Now EXECUTE privilege is granted only to the users U1 and U2.
SQL> GRANT EXECUTE ON P_ACTION TO U1, U2
GRANT succeeded.
GRANT succeeded.
Any other user who tries to
execute P_ACTION ends up getting an error.
CONNECT U3/U3
SQL> EXECUTE P_ACTION('ORCL', 'abcxxx123')
ERROR at line 1:
ORA-01031: Insufficient privileges
ORA-06512: at "SYS.P_ACTION", at line 1
ORA-06512: at line 1
SQL> EXECUTE P_ACTION('ORCL', 'abcxxx123')
ERROR at line 1:
ORA-01031: Insufficient privileges
ORA-06512: at "SYS.P_ACTION", at line 1
ORA-06512: at line 1
3) Using Dynamic SQL with bind arguments
Irrespective of benefits of static SQL, there are situations where use of dynamic SQL cannot be ignored. The scenarios where SQL statement has to be prepared at runtime or DDL execution at execution time is required, dynamic SQL is the only way of achievement.
If PL/SQL code uses Dynamic SQL statements, then instead of passing inputs in concatenated form, it must use bind arguments. This makes a dynamic statement less prone to malicious attacks.
Irrespective of benefits of static SQL, there are situations where use of dynamic SQL cannot be ignored. The scenarios where SQL statement has to be prepared at runtime or DDL execution at execution time is required, dynamic SQL is the only way of achievement.
If PL/SQL code uses Dynamic SQL statements, then instead of passing inputs in concatenated form, it must use bind arguments. This makes a dynamic statement less prone to malicious attacks.
For example, in the below PL/SQL block, V_STMT is the
dynamically created statement which retrieves all the objects owned by an Input
user. Note that it can be attacked by sending bad inputs (using concatenation
and quote operator).
Code (SQL):
BEGIN
…
V_STMT := 'SELECT OBJECT_NAME FROM ALL_OBJECTS '||' WHERE OWNER = '''|| P_USER ||'''';
EXECUTE IMMEDIATE V_STMT;
…
END;
…
V_STMT := 'SELECT OBJECT_NAME FROM ALL_OBJECTS '||' WHERE OWNER = '''|| P_USER ||'''';
EXECUTE IMMEDIATE V_STMT;
…
END;
Above situation can be
bypassed by the use of bin variables. In the Dynamic SQL statement, place
holder variable :1 is given. It would be replaced by the bind argument input
P_USER in EXECUTE IMMEDIATE statement.
BEGIN
…
V_STMT := 'SELECT OBJECT_NAME FROM ALL_OBJECTS '||' WHERE OWNER = :1';
EXECUTE IMMEDIATE V_STMT USING P_USER;
…
END;
…
V_STMT := 'SELECT OBJECT_NAME FROM ALL_OBJECTS '||' WHERE OWNER = :1';
EXECUTE IMMEDIATE V_STMT USING P_USER;
…
END;
4) Validating Inputs using DBMS_ASSERT
Input values can be sanitized before their use to reduce the injections attacks. This can be done using DBMS_ASSERT package. It is an oracle supplied package which contains subprograms to validate the inputs, especially Oracle Identifiers.
Input values can be sanitized before their use to reduce the injections attacks. This can be done using DBMS_ASSERT package. It is an oracle supplied package which contains subprograms to validate the inputs, especially Oracle Identifiers.
Note that DBMS_ASSERT package must be used with SYS schema. Below are the details about the subprograms contained by the DBMS_ASSERT.
Function Description
NOOP No Operation.
ENQUOTE_LITERAL Encloses string literal in single quotes
ENQUOTE_NAME Encloses string literal in double quotes
SIMPLE_SQL_NAME Verifies that the string is a simple SQL name
QUALIFIED_SQL_NAME Verifies that the string is a qualified SQL name
SCHEMA_NAME Verifies that the string is an existing schema name
SQL_OBJECT_NAME Verifies that the string is a qualified identifier of an existing SQL object
Illustration
1. The below PL/SQL block verifies the current schema is CLUB or not. If yes, then it returns the current schema, else control moves to EXCEPTION block.
DECLARE
L_SCHEMA := 'CLUB';
BEGIN
L_SCHEMA := sys.dbms_assert.SCHEMA_NAME(L_SCHEMA);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
L_SCHEMA := 'CLUB';
BEGIN
L_SCHEMA := sys.dbms_assert.SCHEMA_NAME(L_SCHEMA);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
2. The below SQL verifies
the existence of an object.
SQL> SELECT DBMS_ASSERT.sql_object_name('P_GET_SAL') FROM dual;
DBMS_ASSERT.SQL_OBJECT_NAME('P_GET_SAL')
----------------------------------------------------------------------------------------------------
P_GET_SAL
1 ROW selected.
DBMS_ASSERT.SQL_OBJECT_NAME('P_GET_SAL')
----------------------------------------------------------------------------------------------------
P_GET_SAL
1 ROW selected.
3. The below SQL quotes an
input string
SQL> SELECT DBMS_ASSERT.enquote_literal('Club Oracle') FROM dual;
DBMS_ASSERT.ENQUOTE_LITERAL('CLUBORACLE')
----------------------------------------------------------------------------------------------------
'Club Oracle'
1 ROW selected.
DBMS_ASSERT.ENQUOTE_LITERAL('CLUBORACLE')
----------------------------------------------------------------------------------------------------
'Club Oracle'
1 ROW selected.
Limitations
• It can be used in buffer overflow attacks.
• It does not ensure that given SQL name can be parsed or not.
• Cross-site scripting attacks cannot be handled using DBMS_ASSERT.
• It can be used in buffer overflow attacks.
• It does not ensure that given SQL name can be parsed or not.
• Cross-site scripting attacks cannot be handled using DBMS_ASSERT.
