How to prevent your application from SQL Injection?

Avoiding auto generated queries is best method to protect your application from SQL injection or you can use parametrized queries or stored procedures.
For example, a login form has two basic form elements, a text box for accepting a user name and a password box for the password.

< form action="test.aspx">
< input type="textbox" name="user_name">
< input type="password" name="password">
< input type="submit">
< /form>

 

Then if we go by the traditional way of coding,

Dim SQLquery As String = "SELECT Count(*) FROM Users WHERE user_name = '" & user_name.text & "' AND Password = '"& password.text &"'"Dim txtCommand As SQLCommand = New SQLCommand(SQLquery, ConnectionString)

Dim thisCount As Integer = txtCommand.ExecuteScalar()

Above code executes the query which is auto generated. After execution it returns count, if it is greater than zero it means information entered by user is present in database else it is an invalid login.

Considering the above code, suppose someone entered the following string into your password text box:

' or '1'='1

Then the new query would be:

Dim SQLquery As String = "SELECT Count(*) FROM Users WHERE user_name = '" & user_name.text & "' AND Password = '' or '1'='1'"

Dim txtCommand As SQLCommand = New SQLCommand(SQLquery, ConnectionString)

Dim thisCount As Integer = txtCommand.ExecuteScalar()
' or '1'='1

Execution of above query will always give positive login and attacker can easily get into your system and may steal the data or change it.

To avoid this kind of attack we will use parametrized query instead of auto generated query, which is demonstrated in the following example:

Dim txtCommand As SQLCommand = New SQLCommand("SELECT Count(*) FROM Users WHERE user_name = @username AND password = @password", ConnectionString)txtCommand.Parameters.Add ("@username", SqlDbType.VarChar).Value = user_name

txtCommand.Parameters.Add (“@password”, SqlDbType.VarChar).Value = password

Dim thisCount As Integer = txtCommand.ExecuteScalar()

By passing parameters you can avoid many types of SQL injection attacks. You can also use stored procedures to protect your application from SQL injection. Stored procedures secure your database by specific account logins, only these accounts are permitted to execute stored procedures. So, hacker gets restricted access to enter SQL queries to execute against your database. Any transaction to your database would have to be done using a stored procedure which you wrote and is in the database itself, which is usually inaccessible to a perimeter network or DMZ.

Following is an example of stored procedure with authentication:

Dim txtCommand As SQLCommand = New SqlCommand ("proc_CheckLogon", ConnectionString)

txtCommand.CommandType = CommandType.StoredProcedure

txtCommand.Parameters.Add (“@use_rname”, SqlDbType.VarChar).Value = user_name

txtCommand.Parameters.Add (“@password”, SqlDbType.VarChar).Value = password

txtCommand.Parameters.Add (“@return”, SqlDbType.Int).Direction = ParameterDirection.ReturnValue

Dim thisCount As Integer = txtCommand.ExecuteScalar()

Finally, ensure you provide very little information to the user when an error does occur. If there is any database error, don’t give the entire error message. Only provide the necessary information to the users.

This entry was posted in Database, Security and tagged , . Bookmark the permalink.

Leave a Reply