In the first of my guidelines for deadbeat developers, I asked why you’re too lazy to comment your code. This time, I’d like to investigate why you build software that’s catastrophically insecure.
Part 2 – Protect against SQL injection in .NET
This code will look familiar because it’s the sort of sloppy mistake you make all the time.
string productId = Request.QueryString["ProductId"]; string sql = "delete Products where Id=" + productId; SqlCommand cmd = new SqlCommand(sql); cmd.ExecuteNonQuery();
Your feeble imagination doesn’t stretch far enough to consider what happens when a mischievous user sets productId to, say, “1 OR 1=1″. You merrily build the query, complete with unverified user input, and execute it against the database.
delete Products where Id=1 OR 1=1
Oh dear, where did all your products go?
A vigilant SQL Server DBA can thwart your stupidity at the database by restricting your access. By assigning your login to the db_denydatareader and db_denydatawriter roles, you can thankfully be prevented from running any SELECT, DELETE, INSERT or UPDATE queries whatsoever.
Since you can’t be trusted, the DBA should give you permissions to execute only the stored-procedures and UDFs you need. This is the principle of least privilege.
Parameterised stored-procedures are usually safe from SQL injection because they validate the type and size of the inputs. These inputs are evaluated as values only, and not executed as part of the SQL statement. But there is one exception. When you build SQL dynamically inside the stored-procedure.
sp_executesql ’select * from Products where Id in ‘ + @List
This line is from a real stored-procedure I saw last week, @List is a varchar parameter containing something like “(1,2,3)”. And, of course, the values for @List came from unverified user input. If you absolutely have to use dynamic SQL then at least clean the inputs and remove or escape anything that could be potentially dangerous.
Read more about SQL injection:
