What is SQL Injection
According to OWASP, a SQL Injection attack is an attack where the malicious agent (user, bot, etc.) inserts an unexpected query into a client application. The results can be devastating due to the fact that the attack often runs with elevated privileges which can lead to the disclosure of sensitive data, creating admin user in the database, or startup and shutdown the DBMS. SQL Injection is one of many kinds of injection flaws and applications need to do due diligence to protect against them.
Demonstration
The following screen shots detail how to perform a SQL injection attack on a system. For this example, we are using to use WebGoat from OWASP.
In the screen shot above, we see a form that is expecting a user’s account name. Instead, we have supplied the following input:
Smith' or '1'=1
The or ‘1’=1 is the critical portion. Since this application is constructing a SQL string, the where condition evaluates to true and the application prints the entire table to the page.
This may seem like a trivial example, but it’s a good one nevertheless because it’s easy to see the basic methodology of the attack. The attacker is inserting commands into the application. The application is not defensively programmed and therefore doesn’t check for things such as the comment character, the word or, or Boolean expressions such as ‘1’=’1′. The result is that the command is passed to the DBMS and it returns the entire contents of the table.
Additionally, the application fails to validate the output as well. Did we really mean to show the entire database table on this page or just the result of one user account? Also, why does the application have to show fields such as USERID, FIRST_NAME, LAST_NAME etc. We also should not be showing the user anything that represents the internal makeup of the database for both usability and security purposes.
Lastly, we need to consider error handling. Let’s look at these two screen shots.
The first example looks like a regular error message. It’s the second example that’s the problem. In this case, we get “expected token: 1” which is an error message from the database. We never want to show this, for both usability reasons but also security reasons. An attack is going to look at error messages and try and determine the internal makeup of the application. If we aren’t careful, they can learn a lot about your system.
Most developers know not to show error messages like this, but here is one that is often overlooked where the develop showed a user friendly error message on the page, but allowed the stack trace to leak into the response body.
Defending Against SQL Injection
#1 Prepared Statements and Parameterized Queries
Rather than constructing SQL queries by combining strings and sending them to the DBMS, the application should make use of prepared statements and parameterized queries. This will cause the DBMS to treat the parameters and input rather than as executable commands. For example, instead of
query = 'SELECT * FROM USERS WHERE USER_NAME = ' + user_name
Use query parameters
query = 'SELECT * FROM USERS WHERE USER_NAME = ?' cur.execute(query, [user_name])
By using query parameters, the DBMS will treat commands such as ‘1’=’1′ as an input rather than a command and will protect your application.
#2 Stored Procedures
Stored procedures have two benefits. One benefit is that parameters in the query are usually treated as inputs rather than as commands, which helps to keep the application safe. Another benefit is that most database developers do not typically create dynamic SQL in such procedures. Finally, application libraries will often escape content in the parameters that are passed to a stored procedure.
It should be noted that all stored procedures should be properly threat modeled and tested to ensure that they are save to use. Also, it’s critical to make sure that such procedures are run with least privilege when executed. Providing elevated privileges to such procedures can cause them to run amok and threaten the application.
#3 White List User Input
Prior to passing any input to the DBMS, the application should check the input against a white list of allowed values. Any input that is not on the white list should be rejected and considered to be unsafe. For example, if your application is expecting a number, then your white list should contain a list of allowed numbers. This will keep users from supplying text SQL commands.
#4 Escaping User Input
There are a variety of libraries and functions that can escape characters in a SQL string and keep them from being interpreted as commands. For example, your application should escape the line comment character sequence “–” or words such as “WHERE”, “OR”, “UNION”, or “JOIN”
Conclusion
SQL Injection is dangerous, but it is not impossible to protect against. Like most injection style attacks, it’s important that you validate your input and make sure that your application is only sending allowed input to the DBMS. By following the best practices outlined above, you will reduce many areas where your application is vulnerable to SQL injection and other forms of attacks.
Stone Soup Programming 
2 thoughts on “SQL Injection”