Understanding SQL InjectionsScott CosentinoBlockedUnblockFollowFollowingMar 5Code injection vulnerabilities are rated as the most common vulnerability according to the OWASP 2017 Top 10 List.
Most commonly, SQL injections are used to compromise databases and applications, in order to cause data leaks and unauthorized access.
As someone involved in the field of tech, it is essential that you understand this vulnerability, so you can actively prevent it from happening to your applications.
To best understand the vulnerability, let’s build a sample database and application, and see how SQL interacts with the typical application.
Suppose we have a simple SQL database, with a table called users, defined below:Query to create user tableIn this table, we will insert some sample users to have some data to work with:Query to insert users and hashed passwordsWe now have a database similar to one that might be used for authentication on any application.
To see how we can interact with the database, I will create a basic VB.
Here we have a simple login form:Login form for applicationAnd some code that will take input from the form and check if the username and password supplied exist in the database:Dim sqlCommand as SqlCommandDim username As StringDim password As Stringusername = usernameTextbox.
Textpassword = passwordTextbox.
Open()sqlCommand = New SqlCommand("SELECT COUNT(*) FROM Users WHERE Username = '" & username & "' AND userPassword = HASHBYTES('SHA1','" & password & "')", sqlConnection)If sqlCommand.
ExecuteScalar() > 0 Then MsgBox("Login Successful")Else MsgBox("Login Failed")End IfNow, we can test the application to see how the SQL query is built and executed against the database.
If we add a breakpoint, we can step through the application to see the values of each of the variables to better understand what is happening.
To start, the user inputs their credentials, and presses “Login”User inputs credentialsOnce login is pressed, the SQL query to check the user is built by concatenating the username and password inputs into a query, and running it against the database.
The end SQL query looks like this:SELECT COUNT(*) FROM UsersWHERE Username = 'test1'AND userPassword = HASHBYTES('SHA1','password')The important thing to note is how this query is built.
The user input is concatenated directly into the query, then the query is executed against the database.
With the current query, the execution would yield a result of 1, given that there is one record matching the username and password, and this is ok and expected behavior.
But what if a user inputted the character ‘ into the username?User inputs ‘ as usernameNow, the query would be built as:SELECT COUNT(*) FROM UsersWHERE Username = '''AND userPassword = HASHBYTES('SHA1','password')If we let this execute agains the database, we get the following result:SQL exception for queryWe get an SQL exception telling us that our query syntax is incorrect!.So how is this possible?.When the user inputs the ‘ character, it is concatenated to the query, and the query is run against the database.
SQL is interpreting the ‘ character as a closing quote for the username string, causing a mismatch in quotes and therefore a syntax error in the query.
Already, this situation is bad, as the user can cause the program to crash just by entering a single character.
However, it gets worse.
The user can do a lot more damage by altering the query completely.
Consider the input shown below:Malicious user inputIf the attacker were to enter these values, the query that results from it would be:SELECT COUNT(*) FROM Users WHERE Username = ''OR 1 = 1 — 'AND userPassword = HASHBYTES('SHA1','notapassword')Let’s break this query down to understand it.
The filter is going to get all the situations where Username = ‘’, and 1 =1.
Since 1 will always equal 1, this will return all of the records.
The final piece of the input, the two dashes ( — ) is a comment in SQL.
Everything after those two characters is ignored.
The result of this query is to return the count of everything in the Users database.
Since this is going to be greater than 1, the user will be authenticated with access, even though they did not enter valid credentials!User is given accessThis is obviously bad news, as we never want a user to be able to gain access to an application without proper credentials.
So, how do we avoid this issue?Some people might suggest removing any ‘ character, however this might be a valid input (consider a name like O’Reily).
This being the case, there is a better solution.
The best answer to this problem is parameterized queries.
Parameterized queries will generate the SQL execution plan before input is added, meaning it will avoid executing code inputted by the user.
If we wanted to modify our original code to use parameterized queries, we could do so as follows:Dim sqlCommand As SqlCommandDim query As String = "SELECT COUNT(*) FROM Users WHERE Username = @username AND userPassword = HASHBYTES('SHA1',@password)"Dim username As StringDim password As Stringusername = usernameTextbox.
Textpassword = passwordTextbox.
Open()sqlCommand = New SqlCommand(query, sqlConnection)sqlCommand.
Value = usernamesqlCommand.
Value = passwordIf sqlCommand.
ExecuteScalar() > 0 ThenMsgBox("Login Successful")ElseMsgBox("Login Failed")End IfDoing this will solve our issueUser is now rejected accessThe parameterized version of the query is significantly more secure, and will help you be safe from SQL injection attacks.
It is important to use this method when accessing SQL databases, otherwise your data may be destroyed or improperly accessed by attackers!.