The most common way for people to insert invalid data or cause damage to your database is through what is called a SQL Injection Attack. This is when malicious code is placed within the responses which are expected and that code is not caught and it instead executed. Depending on what level of access to the SQL Server your application account has will determine how successful the injection attack will be.
There are ways to protect your self. The first and most important is that the account which the application uses to connect to the database should have the least set of rights necessary to do it’s job. Don’t make the account dbo or sysadmin just so that everything works correctly without you having to modify the rights when you add objects. The second requires your front end developers (the ASP, .NET, etc folks) to understand the potential problem so that they code the application in such a manor that the risk is reduced or removed.
(Please keep in mind that I’m not an application coder so my application code shown below may not be perfect.)
If they use in-line T/SQL commands such as this:
dim v_ado, v_sql, v_conn
v_conn = "Provider=sqloledb;Data Source=sqlserver;Initial Catalog=AdventureWorks;User ID=UserName;Password=password;"
v_sql = "exec usp_Something @var1='" & request.form("Var1") & "', @var2='" & request.form("Var1")
set v_ado = CreateObject("ADODB.RecordSet")
on error resume next
v_ado.open v_sql, v_conn, 3, 3
...
Then you are open to an injection attack. This is because I can simply put “‘; SELECT * FROM sys.tables” as one of the form fields which I am submitting back to you and my SELECT statement will be executed against your SQL Server. Depending on how your rights are setup will then determine how successful my command is. The configuration of your UI will then determine what data if any is returned back to me through the UI. If I find that I have rights to do stuff and I can get the UI to return data to my I can then have the SQL Server return me your customer data, or if I’m feeling like causing you some headaches I can drop all your tables by using the correctly crafted code.
The best way to not be open to these attacks is to use parametrized code on the front end. This allows you to pass in your variables but without leaving the site open to attack as a T/SQL command isn’t passed directly to the SQL Server. This is done using the technique shown here.
Dim v_ConnString As String
v_ConnString = "Server=servername;Database=AdventureWorks;uid=username;pwd=password;"
Dim v_Connection As New SqlConnection(v_ConnString)
Dim v_Command As New SqlCommand("sp_helpdb", v_Connection)
v_Command.Parameters.Add("var1")
v_Command.Parameters("var1").Value = request.form("Var1")
v_Command.CommandType = CommandType.StoredProcedure
v_Connection.Open()
If your developers insist on using the first method all is not lost. There is still a method which can be used to help protect the database. Each value which is returned from the end user has to be validated. Any single quote values should be replaced with two single quote values. This way anything which the malicious code executor tries to push through is turned back into a string value. Also all inputs regardless of data type should be surrounded by single quotes. If this isn’t done then when passing in a number value the attacker can easily run there own code without having to get caught by the single quote check.
dim v_ado, v_sql, v_conn
v_conn = "Provider=sqloledb;Data Source=sqlserver;Initial Catalog=AdventureWorks;User ID=UserName;Password=password;"
v_sql = "exec usp_Something @var1=" & request.form("Var1") & ", @var2='" & request.form("Var1")
set v_ado = CreateObject("ADODB.RecordSet")
on error resume next
v_ado.open v_sql, v_conn, 3, 3
...
If I then set the value of the Var1 form element to “6; select * from sys.tables” I will be able to query all the tables and my code won’t ever be caught by the single quote check.
Unforunately once the data has been submitted to the database, there isn’t much which can be done within SQL to decide is there is malicious code within the string. Mostly because your stored procedure probably won’t ever see the malicious code. The attacker will terminate the stored procedure and run their command after (using the technique I showed above) so any potential checking that you did within the procedure won’t do anything.
These techniques are not foolproof. The best protection method against an injection attack is to parametrized code method shown above. No matter what the attacker places in the form fields it won’t be executed as the engine (ASP, .NET, JSP, PHP, etc) will simply take the value and add it as a parameter to the procedure no matter how the attacker tries to escape out to run there own code. Protection the database from an injection attack is a shared responsibility between the DBA and the front end developer. Neither person or team should try to put the entire responsibility on the other team. DBAs need to make sure that object rights are as minimal as possible, and front end developers need to make sure that the commands never get sent to the database in the first place.
Have you seen any other techniques which can be used to protect the database. Share them in the comments below.
Denny