SQL Injection Protection Methods

Introduction

In this article, we are going to look at SQL Injection Protection Methods. SQL Injection is one of the most dangerous issues any organization can ever go through because it threatens organizational and customer confidentiality and integrity.

For us to be able to counter or prevent SQL Injection we need to look closely at how each type of Injection is implemented to figure out how best to avoid the implemented method.

To start with we will look at the means of injection which in this case is SQL statements. Every type of injection is manipulating a SQL statement to pass a malicious statement. So can this be avoided? Yes. It is very true that SQL injections have remained a huge threat to web development and preventing them has not yet proven to be 100% accurate but in the meantime, there are some measures and vulnerabilities that we can handle and still be to limit the exposure to injection attacks.

Prevention of SQL Injections

In-band Injection (Classic) includes Error-Based Injection and Union-based injection. With this type of injection, the attacker uses the same channel to launch and gather information. The biggest vulnerability in this attack is dynamic SQL statements either in a simple statement or a stored procedure. Often developers assume that Stored Procedures are not injection targets but they are if they are implemented dynamically. In a secure application, it is handy to use a parameterized query which behaves as if it's pre-running the query with placeholder data in the input field, and this way the server knows what command to expect. This way the query will not be altered outside the expected variables (i.e. a SELECT statement cannot be changed to a DROP statement).

Example

EXEC SelectAllCustomers @FulName = '" + parameter1 + "', @PostalCode = '" + parameter2 + "'

A Stored Procedure implemented in this way will still be open to injection attack because of its dynamic nature.

Since a lot has been tried and has failed, the most secure way to protect our web applications from Classic Injection is to use strict validations on user input. This has its disadvantages because it might restrict users when they using the application but it is the safest way to avoid Classic injection.

In Asp.net applications one can use the following Validation Control to monitor user input.

<asp:RegularExpressionValidator ID="RegularExpressionValidator1" ControlToValidate="TextBox1" ValidationExpression="[a-zA-Z0-9]*[^!@%~?:#$%^&*()0']*" runat="server" ErrorMessage="Invalid Input" ForeColor="Red" Font-Size="XX-Small" Display="dynamic" Font-Italic="true"></asp:RegularExpressionValidator>

The above control will ensure that no special characters are included in the user input and will show a client-side response to inform the user that the input cannot be accepted.

And this will only allow letters and numbers which basically cannot be used in the injection. This may be seen as a disadvantage given that there some unique names such as ‘Ren’ee’ with special characters in them and this might limit user flexibility when using the web application.

Other than this we should also bear in mind that databases have their own security features which include READ and WRITE permissions so it is very important to ensure that our database cannot allow READ and WRITE permissions to UNKNOWN logins. You can find more on MSSQL permissions via this link.

Microsoft also put out an SQL Injection Inspection Tool that sniffs out invalidated input that is contained in the query. There are also other tools available online to search and scan your web application or website for vulnerabilities. They test your application using different types of SQL injection techniques. This will allow you to know the weaknesses and fix them beforehand.

The use of a Web Application Firewall for web applications that access databases can help identify SQL injection attempts and may help prevent SQL injection attempts from reaching the application.

Another safety precaution would be to encrypt passwords in the database. Password hashing is a safe way of ensuring that confidential passwords are not stored in the database as plain-text as this could also culminate into an internal threat if an untrusted member of the organization has access to the database. Encrypting passwords on insertion may be one way to ensure that the malicious attacker will not gain access to sensitive information.

The following snippet shows an insert statement that makes use of one-way hashing and encrypts just the input given by the user and this makes the database safer. There are many other ways of encryption which are supported by MS SQL Server.

INSERT INTO dbo.[tblUser] (user_login, PasswordHash, FullName, Dept)
VALUES(@user_login, HASHBYTES('SHA2_512', @PWD), @pFullName, @Dept)

Data in the table will look like this,

Another tip is to use error messages that reveals little of what is happening internally. In Try...Catch statements it is wiser to use a custom message once an exception has been encountered.

Example

Try
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("Constring").ConnectionString)
Using cmd = New SqlCommand("SaveBeneficiary", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Surname", txtBenSurname.Text)
cmd.Parameters.AddWithValue("@firstName", txtBenfName.Text)
cmd.Parameters.AddWithValue("@sex", cmbSex.SelectedValue)
cmd.Parameters.AddWithValue("@IDNum", txtBenIDNO.Text)
cmd.Parameters.AddWithValue("@Marital_Status", cmbBenMaritalStatus.SelectedValue)
cmd.Parameters.AddWithValue("@DOB", txtBenDOB.Text)
cmd.Parameters.AddWithValue("@Address", txtBenAddress.Text)
cmd.Parameters.AddWithValue("@Phone", txtBenContact.Text)
cmd.Parameters.AddWithValue("@Employer", txtBenEmployer.Text)
cmd.Parameters.AddWithValue("@Relationship", cmbRelationship.SelectedValue)
cmd.Parameters.AddWithValue("@PolicyNum", txtPolicyNo.Text)
cmd.Parameters.AddWithValue("@isDeceased", 0)
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Open()
cmd.ExecuteNonQuery()
Response.Write("<script>alert('Beneficiary Details Successfully Saved') ; location.href='customer_registration.aspx'</script>")
Catch ex As Exception
MsgBox("Error")
End Try

It is wiser to use the ex.Message for internal debugging and show little information to the users for protection.

For attacks such as Out-of-band injection you would want to ensure that your application does not have the following weaknesses:

No network security parameters to restrict DNS or HTTP outbound requests.
Sufficient privileges to execute the necessary function to initiate the outbound request.
Lack of input validation on a web application.

Once these vulnerabilities are taken care of, it will ensure that your data is not attacked and accessed using an unknown domain, thus your application will be safe from Out-band Injection.

Conclusion

Prevention from SQL injection can also be countered through the use of modern and trusted technologies but all the information above can help you prevent your application from unauthorized and unwanted access.