SQL Injection – It’s Still Around
Recently I received an email from a colleague consulting for a company that has exposure for SQL Injection. They use a parameterized stored procedure, and determined that they had resolved their exposure to SQL Injection .
SQL Injection occurs when a user embeds SQL Statements in input fields of an application or web form. They simply find a column that has a text value, insert a close quote and command termination character ; followed by their own SQL statement.
Applications that use parameterized queries or parameterized stored procedures usually ignore any injection text entered and store it like any other text.
Applications that build SQL Statements without taking this process into account are at risk. Take the following C# code for example:
string SQLStatement = string.Format("SELECT * FROM SomeTable WHERE SomeColumn = '{0}'", SomeVariable);
Here is the result of this statement with an un-injected value using the C# code query builder above
string SomeVariable= "A";
SQLStatement = "SELECT * FROM SomeTable WHERE SomeColumn = 'A'"
Here is the result of this statement with an injected value
string SomeVariable= "A';SELECT * FROM MASTER.DBO.SYSDATABASES";
SQLStatement = "SELECT * FROM SomeTable WHERE SomeColumn = 'A'; SELECT * FROM MASTER.DBO.SYSDATABASES";
If that’s all they did, you’re lucky; they just got a list of all the databases attached to your SQL Server instance, assuming the account running the query has appropriate permissions.
Back to the issue pointed out by my colleague. They are consulting with a company that has a centralized stored procedure that performs updates in their database. The stored procedure has one parameter; @SQLStatement VARCHAR(MAX). As I understand it the procedure looks as follows.
CREATE PROCEDURE spUpdateTable (@SQLStatement)
AS
EXEC (@SQLStatement)
Now they have tons of code out there using this single stored procedures. This is no different than building dynamic SQL and running it through ADO as text input rather than SQL Server input with a parameter. The same SQL with injection I built above may now be executed through this stored procedure. So, simply because they are using a parameter as input to a stored procedure, there is no protection against SQL Injection. All that has changed here is the mode of transport to the database engine.
My purpose in posting this issue in the newsletter today is twofold. First, I want to remind you that SQL Injection is still possible today. Learn to use a good Data Access Framework…there are plenty to choose from. Better yet, learn how to use parameterized queries or stored procedures with type safe parameters that can’t be hijacked for nefarious activities.
Second, my colleague is in a position to help the company to resolve this issue. However, like Y2k problems, they can’t just shut down the business. They have help with long term solutions, which means replacing a lot of code.
But they need a solution for the short term also. I have some ideas myself. I’d like to hear from you first. What would you do in this situation? What advice would you provide my colleague to solve the immediate problem short of turning off the database?
Send your solutions to me at btaylor@sswug.org.
Cheers,
Ben
SelecTViews
With Stephen Wynkoop and Sam Brace
Find out about business intelligence trend surveys, MySQL in the cloud…the latest news and your SQL Server BI tip for the day.
[Watch the Show]
$$SWYNK$$
Featured Article(s)
SQL Performance Troubleshooting using Standard Reports
Learn how the Standard Reports can be leveraged to troubleshoot performance problems, in case you don’t have Performance Dashboard installed on your server.
Featured White Paper(s)
Web Content Management
The power of the World Wide Web has dramatically changed the way companies conduct business with their customers, partners, … (read more)