I am attending a Cyber Security course provided by CodePath and sponsored by Facebook (thank you). During SQL Injection week I found myself very frustrated. I felt like I did not have a process that I followed and instead was copy-pasting various commands in hopes that one will eventually work.
I wanted to share the process that I eventually ended up following. This post is targeted towards students learning SQL Injection, in hopes of making the learning experience a bit less frustrating.
1. Identify Injection Point
All SQL Injection questions have a vulnerability, otherwise you wouldn’t be asked to solve them. 95% the vulnerability can be confirmed by either submitting
". You can tell you found a vulnerability when server returns an unusual error. Ideally it would actually forward the SQL query error, but realistically it would probably just say something “Opps, something went wrong”. Still this is a great sign, because this message is different than let’s say “No results were found”, which would indicated that query worked as expected.
If single or double quote didn’t work, try to find a different injection point, for example try escaping the singe or double quote (
If you can’t get query to fail, it’s possible that it is just failing silently under the hood and you would have to do a blind SQL Injection (good luck). In my experience 99% of SQL Injection question would have an identifiable injection point.
2. Record your inputs and error messages
After the injection point was identified, I would try to further narrow down the space of characters that I can work with. Usually an SQL Injection example would return a small number of errors, so inputs can be easily groups by the error that they are returning. For example app can have a “Query failed” message, a “User not found” message and “Invalid Parameter” message. I would write down each message as a bullet point and list the query that caused the message underneath it. In most cases an identifiable pattern would begin to emerge.
For example, my sample input/output for the three messages above could look as follows:
'is likely needed)
- User not found
"– valid input and not an injection point
admin– valid input
- Invalid parameter
- (empty space)
I can quickly tell by looking at this list that my solution will include
' and will not include
(, #, ; and an empty space. This awareness greatly reduces the space of SQL Injection combinations that I have to try and makes my life a lot easier.
3. Try to guess the SQL Statement being executed
I noticed that all my exercises were using MySQL as their database, so I used a local copy of MySQL to run my experiments. I would look at input fields of each exercise and would try to guess the query that was happening under the hood.
For example if I just saw a user name input, I would assume that the query would be something like:
SELECT * FROM users WHERE name = 'alex' LIMIT 1;
I would first write an actual query against similar table in my database. Then I would test that my SQL Injection command would actually work. So in example above I would convert it to the following query, and test that it would in fact return all users.
SELECT * FROM users WHERE name = '' or 1=1; -- ' LIMIT 1;
With my injection code being
' or 1=1; -- everything between the original
' quotes. Once I confirm that it works in my DB, I can test the query against the example.
If database is not known, I think it still a good idea to pick any SQL Database as a starting point. Adjustment can be made later down the road, when database specific behavior is identified.
4. Traverse Database using Union Select
Once you have a successful injection query, usually all of the database can be traversed using
union select. There are good guides for this process available online, but in a nutshell here are the steps to follow.
- Find number of columns in original query via
union select 1;
union select 1,1;
union select 1,1,1;etc. This query will fail, until number of 1s matches number of fields returned by original query.
- Replace one of 1s with
limit 0,1;to return first matching record,
limit 1,1;to return second matching record etc.
- Use this method to get all table names and column names in that Database.
For example this injection query, will return first table name for database against which query is executed.
'UNION SELECT 1,table_name FROM information_schema.tables WHERE table_schema=database() LIMIT 0,1; --
and this will return second column name (because of LIMIT 1,1) for table
'UNION SELECT column_name FROM information_schema.columns WHERE table_name="some-table" LIMIT 1,1; --
Some other tips
Bypass front end validations
A lot of example will add additional Front End validations to your input. This should be bypassed by capturing and editing actual requests being made with such tools as Burp, Developer Tools in Browser (i.e. Firefox has edit and resend functionality), Postman, or other similar tools.
GROUP_CONCAT can be used with
UNION SELECT to return more than value at a time. The following example will returned many table names (subject to string size limit) concatenated into one string.
UNION SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema=database(); --
Learn and Use Automated Injection Tools
Our course instructor said, learn to do it by hand and then learn to use the tools. Both are important. One amazing tool that we were shown was Sqlmap.
This tool will do the steps that I outlined above and much more. Once injection query is identified, the tool can be used to automate interaction with the database (like using
union select above and much more).
Here are sqlmap flags that I used:
-u– URL of the request (from Browser developer tools or Burp)
--cookie– my exercises were behind authentication, so having a session cookie was a must
--data someField=someVaue– All my examples were using POST requests. GET is default for Sqlma, but
--dataflag will let Sqlmap know what to send in the body of the request and will trigger POST request.
-v 5– Bumps up verbosity to max level to see requests being made by Sqlmap.
--dbms MySQL– Only try to inject MySQL database. Since I knew the DB in our examples, this flag greatly reduced time of attack as well as number of request being made to our course server. In real life it can help avoid being detected by making too many requests.
--eval– Sqlmap has a lot of built in tamper scripts, that can modify (i.e. encode etc) user input prior to sending the SQL request. If those scripts are not enough
--evalflag can be used to run any Python code on the input value before it’s being sent over the wire.
--sql-shell– once injection is identified, this flag turns Sqlmap into SQL command line interface, where all of SQL commands (i.e.
"SELECT * FROM some_table;") can be performed.
--search– These and many other flags that will collect and output data from the database (after vulnerability is found).
Hope you find this post helpful. Happy Hacking!