Preventing SQL Injection with paramaterized queries

I use paramaterized queries when using ad-hoc queries in my code-behind. Everywhere website I visit says to use stored procedures or paramaterized queries if stored procedures cannot be used. I understand how SQL injection attacks work, but could someone please enlighten me why a paramaterized query helps prevent these attacks? It seems to me that the text that is entered on the web form would just be passed into the paramater, but I'm obviously missing something. Thanks.

Hi,

If you run the below statement it returns all the records.

declare @.pwd nvarchar(50)
declare @.username nvarchar(50)

set @.username = N''' or 1=1--'
set @.pwd = N'xxx'

declare @.sql nvarchar(1000)
SET @.sql = N'select username, password, userid from Users where username = N''' + @.username + ''' and Password = N''' + @.pwd + ''''

exec sp_executesql @.sql

select @.sql

But if you use create a procedure like below.

create proc login (
@.pwd nvarchar(50),
@.username nvarchar(50)
)
as
select username, password, userid from Users where username = @.username and Password = @.pwd
go

And run the below code, it returns nothing.

declare @.pwd nvarchar(50)
declare @.username nvarchar(50)

set @.username = N''' or 1=1--'
set @.pwd = N'xxx'

exec login @.pwd, @.username

In stored procedures parameter values are exactly used as values of the parameters, but if you are creating dynamically sql statements the final sql sentence may change according to the entered values

|||

Eralper is right, that's why. If his explaination wasn't clear enough, try this:

SELECT * FROM MyTable WHERE field='value' OR 1=1 OR ''=''

This would be similiar to someone building a SQL String like:

SQL="SELECT * FROM MyTable WHERE field='" & textbox1.text & "'"

and then someone types invalue' OR 1=1 OR ''=' into the textbox. This will return every row in MyTable instead of just the one you wanted. You could do sorts of things as well, like typingvalue' DELETE FROM MyTable SELECT ' will delete the contents of MyTable on you, etc. But this won't work:

DECLARE @.MyText varchar(8000)
SET @.MyText='value'' DELETE FROM MyTable SELECT '''
SELECT * FROM MyTable WHEREfield=@.MyText

This will return no records at all, because field never matches @.MyText (unless you have that weird value in a field)

Because @.MyText is a parameter value, it will not even attempt to execute or use anything within it as a command, partial command, etc. It is a value, and it can't be anything other than a value.

|||

Sql injection really has me freaked but I'm trying to find an effective/efficient way of writing dynamic sql.

This this instance: You have a GridView binded to an ObjectDataSource with some TextBoxes used for Parameters that Filter the GridView. The GridView allows Sorting on all Fields and Custom Paging is implemented in a Sql Stored Procedure because the DataSource contains 100 million records.

With Custom Paging, Sorting must be done in the Stored Procedure. My issue is finding an appropriate method to allow Order By and Where to be dynamically created. Not all Fields would necessarily be filtered and the Order By may sort multiple Fields.

This would be easiest to do with Dynamic Sql, but I can't fathom the risk of exposing too much information because of an injection attack.

The only other option I know of is to use CASE statements in the SProc but that ends up being nasty and huge.

My concern with injection is the fact you have no idea what could be coming through. It may be a simple single quite or a hex derivative. You might say to use Regular Expressions to validate the input, but when filtering, some of the Fields may be free-text Fields.

Any thoughts related to this?

Nathan

|||

Hi,

One more step further than the dynamic sql, if you have 100 million records and if you will let the user to sort the data on any column and filter on any column will cause the indexes on the sourcce tables to be insufficiently used or will not be used perhaps. So you will struggle with the performance problems. So you will limit the columns that the users will sort on or filter on, and will create indexes on those fields, otherwise performance will be a headache.

One more alternative is creating sp's for each condition. But this may be impossible if you have so many conditions.

As far as I remember, I once faced a problem with "order by" clause which was related with the record size. Since it was too big for sql server to manage an order process on it. So you can perhaps first build a table with only identity columns and necessary fields to be build for an order. And if you do not need a joined table for an order process do not include it. After managing the order by process, you can gather the other fields that will be displayed on the grid using the primary key values.

Eralper

http://www.kodyaz.com

|||

I don't think I understand what you mean by the Indexes to be insufficiently used. Assume that each Field has an Index set.

A quick question too now that I think about it. I saw in an instance of one of my databases where there were some indexes set to 1 field while some were set to multiple fields. How does this affect the order by clause? Let's say 3 fields, A, B and C each have a single index set on it.

If I order by A and B then are their indexes inconsequential since both are being ordered? Is that why you would have indexes with multiple fields. If that is the case, then you're right about limiting their sorting capabilities as this one table that has millions of records also has over 20 fields and doing combinations of indexes would be gnarly.

If that is the case that multiple field indexes must be created for multiple order by field parameters then I will stick with single indexes and order by field parameters.

It just came to mind as you can order multiple columns in an Excel Worksheet, I thought I'd allow that in a GridView.

Nathan

|||

Hi,

For multiple field indexes, let's say you select A and B from Table1 and you have index on A

For the criterias or the sorting, the SELECT will reference the index A then will go to physical data pages and get the column B values.

But if you have an index both covering A and B, then the SELECT can get all information from the index and will not go the physical data pages. This will be very fast when compared with reading data pages. The end point of this approach is creating covering indexes.

For the first part of your reply, if you have an index for each colum you display on the grid then no problem. But if you display field A, and there is not an index on A. Then to filter all data in table for a specific value on field A, you will have do full table scan or at least a clustered index scan will run on the table.

Eralper

http://www.kodyaz.com

|||

So... Stored procedures provide protection against sql injection attacks... But what about parameterized queries?

I've always assumed that parameterized queries, even those generated dynamically, provide the same level of protection as stored procedures. Is this a proper assumption?

|||

Yes, you are right. As I know they are simply the same like running a stored procedure. They can be used against sql injections.

Eralper

http://www.kodyaz.com