SQL Injection 101

 

SQL injection attacks occur when someone executes malicious SQL statements (also called payloads) that control a database server, typically in a manner not favorable to you. Any SQL-based database is vulnerable to these types of attacks, and it is one of the oldest, most common, and most dangerous forms of attack that someone can direct toward your database. All it takes is one failure for an entire database to be compromised. In some cases, the attacker can even run commands against the operating system of your server through your database.

This post covers more about how a SQL injection attack works and how you can prevent this from happening.

 

How SQL Injection Works

Let’s say that you have a URL with a query string parameter that resolves to this:

SELECT * FROM yourTable WHERE id = 1

 

The URL probably looks like this:

https://example.com/foo/?id=1

 

However, if this is all there is, you can quickly change the function of the SQL statement. Someone could quickly change your URL to

https://example.com/foo/?id=1 or 1=1

 

Resolving the URL leads to the following executed against your database:

SELECT * FROM yourTable WHERE id = 1 OR 1=1

 

Rather than returning a single record, the user has successfully retrieved all of the records in the database. On the flip side, someone could test your site for vulnerabilities by testing.

SELECT * FROM yourTable WHERE id = 1 AND 1=1

 

If no records come back, then the person knows the site is vulnerable.

This is, in a nutshell, what a SQL injection is — changing the query executing against a database with untrusted and unexpected data.

 

Getting More Data

If editing the WHERE clause doesn’t return enough information, the hacker can append an additional statement using the UNION operator. Such attacks are known as union-based SQL injection.

Let’s say that someone knows that your database is vulnerable and decides to navigate to the following URL:

https://example.com/foo/?id=1 union all select * from usersTable

 

The SQL query against your database then looks like

SELECT * FROM yourTABLE WHERE id = 1 UNION ALL SELECT * FROM usersTable

 

UNION operators only work when the tables have the same number of columns, but figuring this out is just a guess-and-check exercise.

As we previously said, it only takes one failure to compromise an entire database.

 

Using Errors to Get Specific Information

When reading the above examples, you probably wondered what someone could do with such limited amounts of information. The answer to this is to use error messages to infer information about your database structure. These attacks are known as error-based SQL injections.

Error messages contain helpful information when troubleshooting issues relating to your infrastructure, but the same information in the wrong hands can be dangerous. By passing in problematic data to your database and making guesses based on the errors that your server returns, someone can easily guess enough about your schemas to inject queries returning more specific information.

Because SQL errors are so descriptive that people can make such inferences, you should disable them on a live website (or log the errors to a file with restricted access).

 

How You Can Protect Your Databases Against SQL Injection Attacks

The easiest way to prevent SQL injection attacks is to use static queries since it doesn’t include user-controlled input data. However, this can be an unrealistic expectation, especially when you’re working with things like authentication/authorization. As such, here are three commonly recommended ways to protect yourself against SQL injection attacks. The basis of everything that follows is to assume that any data that enters your system, regardless of where it came from, is malicious until proven otherwise.

 

Use Prepared Statements with Variable Binding

One recommended solution for protecting yourself in such a scenario is the prepared statement with variable binding, which requires you to define the SQL query, then the data (in that order). This is done within the application itself.

PREPARE prepStmt FROM
‘SELECT * FROM yourTable WHERE id = ?’
SET @idNum = ‘123’;
EXECUTE prepStmt USING @idNum;
DEALLOCATE PREPARE prepStmt;

As you can see in the example, the statement comes before the variable that will contain the user’s input. This allows the database to distinguish between the two, regardless of the user input, so that the attacker isn’t able to change the intent of the query. For example, if given the input 1 or 1=1, the prepared statement looks for a user whose ID matches.

 

 

Use Stored Procedures

Stored procedures are another option for protecting yourself against SQL injection. When implemented correctly, stored procedures typically have the same constructs as prepared statements. As such, the primary difference is that stored procedures are defined and stored in the database itself.

 

As an example, we can turn the prepared statement above into a stored procedure that can be stored in our database.

DELIMITER //
CREATE PROCEDURE strdPrcdr( IN val INTEGER(10) )
BEGIN
PREPARE prepStmt FROM
‘SELECT * FROM yourTable WHERE id = ?’
SET @idNum = val;
EXECUTE prepStmt USING @idNum;
DEALLOCATE PREPARE prepStmt;
END//
DELIMITER;

Use Whitelist Input Validation

In instances where you cannot bind variables, such as table or column names, you can use validate input. There are two ways to do this:

  • Blacklisting: By creating a blacklist, you compare input against a list of values that are not allowed. For an effective blacklist, you have to consider any potential malicious input, and as people discover new techniques, you need to respond by updating your blacklist accordingly.
  • Whitelisting: The flip side of blacklisting is a whitelist, where you compare input against a list of values that you’re expecting for a given field (for example, email fields use RegEx to determine if the input data has the format of an email address).

Blacklists certainly have their place, but because validating input against whitelists is both easier to implement and more secure, we recommend this approach.

 

Takeaways

Despite the fact that SQL injections are one of the oldest and most common attacks launched against sites and apps, many fail to secure themselves against these threats. It only takes one vulnerability to be breached for your database to be compromised, and the fallout can be severe. To protect yourself, you need to learn how to prevent these attacks. Remember, an ounce of prevention is worth a pound of cure (if not more).

 

Image source: XKCD