SQL Injection (SQLi) Cheat Sheet, Attack Examples & Protection

SQL Injection, sometimes shortened to SQLi, is perhaps the most commonly employed hacking technique today, constantly making headlines and appearing in vulnerability reports. These malicious injections have been regularly starring in the OWASP Top-10 lists for years and they took the first place in the 2013 OWASP Top-10.

Before diving into how to prevent SQLi, it’s very important to understand the origins of SQL. We are looking at a programming language designed especially for managing information and interacting with databases. SQL has been in constant use since the mid-80s, but has not quite adapted to the huge changes and advances in technology.

A brief introduction to the SQL language:

SQL can be broken down to major language elements – Queries, Clauses, Expressions, Predicates and Statements. For example, expressions produce scalar values or plain tables that consist of columns and rows of data. Most importantly, queries retrieve data based on specific criteria and are widely used in hacking procedures.

The command most relevant to SQL Injections is “and 1=1”, which always returns a positive answer. This feature is almost always used while hacking into SQL databases. Apostrophes and Semicolons also are integral parts of the SQL grammar and feature extensively in SQLi exploitations.

What is SQL injection?

Generally speaking, SQLi are unsanitized user input vulnerabilities. The most common exploitation is in log-in fields of unprotected web and mobile applications. Since all modern applications (web and mobile) use centralized databases to deliver and render information, such hacking opportunities exist in virtually all leading e-commerce, social and financial websites and applications.

SQLi are basically SQL commands that are maliciously injected into SQL statements via an unsanitized input field. This is how the hackers illegally communicate with the application’s database, harvesting sensitive information and assuming control of the application for their personal benefit.

A brief SQLi example:

Below is a sample website that demonstrates how an SQL Injection is used.

This is a basic website with a text field and a button. The purpose of this search field is to look for books in the inventory. But the hacker can use this input field to gain access to the database with the use of SQL Injections. Advanced hackers can delete database entries and in extreme cases also cause the aforementioned website to crash.

How is it done?

The character “‘” is entered into the search field and pressing the button leads to an error page which displays more information than needed.

This example showcases a badly and insecurely programmed application that is incapable of handling SQL Injections. Just a few illegal characters with a little sniffing around leads the hacker to this string: “‘ union select password from users;”. He can then implement this finding to harvest usernames and passwords from the database.

This of course is just one basic way to exploit application databases. Data hacking and extraction can also be easily performed and automated with commonly available third party software such as SQL Ninja and SQL Map.

SQL Injection Demonstration:

What are the damages caused by SQL Injections?

  • Stealing of usernames and passwords for commercial or criminal purposes.- Complete wiping out of content or defacing of website pages (i.e hacktivism).
  • Silent spying and monitoring of information by competitors.
  • Corruption of entire databases and deleting of backups.
  • Obtaining the application’s admin credentials via an SQL injection can have serious consequences, including taking control of the server remotely, manipulation and exploiting of the applications already residing on the server and also the gaining of unauthorized access to other servers on the network.

How do you ensure your code cannot be hacked with SQL Injections?

In order to avoid SQL injections, it is imperative that any input the application receives is sanitized to validate that what the application is expecting to receive as input. For example, a book name should indeed be a book name and not a malicious SQL script.

Most programming frameworks have input sanitization methods that should be used whenever possible. In addition, the following security steps should be taken to neutralize SQL injections:

  • Validation should be based on a whitelist: accept only data fitting a specified structure, rather than reject bad patterns.
    Check for the following:
    – Data Type
    – Size
    – Range
    – Format
    – Expected values
  • Instead of concatenating strings:
    – Use secure database components such as stored procedures, parameterized queries, and object bindings for commands
    – An even better solution is to use an ORM library, such as EntityFramework, Hibernate, or iBatis.
  • Restrict access to database objects and functionality, according to the Principle of Least Privilege

How to Prevent SQL injection attacks?

Checkmarx’s Static Application Security Testing (SAST) solution automatically scans your application’s source code, mapping the data flow from the input to the sink. Flows that do not include a sanitation method are automatically flagged as potential SQL injection vulnerabilities.

Sample code in SQL:

select id, firstname, lastname from authors

If one provided:

Firstname: evil’ex Lastname: Newman

The query string becomes:

select id, firstname, lastname from authors where forename = ‘evil’ex’ and surname =‘newman’

Which the database attempts to run as:

Incorrect syntax near il’ as the database tried to execute evil.

A safe version of the above SQL statement could be coded in Java as:

String firstname = req.getParameter(“firstname”); String lastname = req.getParameter(“lastname”); // FIXME: do your own validation to detect attacks String query = “SELECT id, firstname, lastname FROM authors WHERE forename = ? and surname = ?”; PreparedStatement pstmt = connection.prepareStatement( query ); pstmt.setString( 1, firstname ); pstmt.setString( 2, lastname ); try { ResultSet results = pstmt.execute( ); }

Preventing SQL Injection with CxSAST

Since applications can have many data flows, CxSAST points at the optimal location in the code to place the sanitation method. This helps in eliminating as many potential SQLi exploits as possible with a single fix. Checkmarx presents each SQL Injection finding as an attack vector, making it easy to traverse the code from input to sink and see the flowing of corresponding lines of code the data.

This allows a security professional or a developer to understand how this attack can be perpetrated.

How Checkmarx CxSAST Detects SQL Injection: