Hacker News Reader

Comment by timwiseman | Hacker News Reader

First, just checking the data is often not enough and can be hard to do right. The better option normally is to paramaterize the query which makes it very difficultto prevent it.

And no, not everyone paramaterizes or validates their data at all.

I wrote about some of the defensive techniques at http://www.simple-talk.com/sql/learn-sql-server/sql-injectio... a couple of months ago.

View on HN

Exactly, and what does 'checking' even mean in this case. If you're not parameterizing your SQL queries and relying on your home baked string/regex/etc. checks then you've already lost.

I am honestly baffled by developers who think they can sanitize input enough to happily send it to SQL without parameters. Can anyone explain why this would ever be a good idea?

On a side note I also don't understand why anyone would give an app permission to do anything other than read/update data. Honestly if your app can drop tables then isn't it a bit broken from a security standpoint anyway?

Normally i hate "This" posts, but HN does not display my upvote, so i'm writing this comment to reiterate how important this is.

This is the right way to do it. Paramaterize your queries, do not ever use string concatenation to generate SQL queries. If you try to sanitize the inputs yourself you will fail at some point.

Let me be more clear here by what is meant by 'parameterizing' a SQL command.

So, looking at some of my old code I see in Visual Basic .NET, I start by 'declaring' the types of, and allocating storage for, three identifiers:

    Dim my_SQL_command_string As String

    Dim my_SQL_INSERT_command As New SQLCommand

    Dim new_instance_values_sum As Long

Then I build the 'command text' of a SQL command string:

    my_SQL_command_string = _
      " INSERT Instance_Value_Averages " & _
      " ( Instance_ID, Instance_Values_Sum, N_Instance_Values ) " & _
      " VALUES " & _
      " ( @Instance_ID, @Instance_Values_Sum, @N_Instance_Values ) "

In this string are three 'parameters':

      @Instance_ID
      @Instance_Values_Sum
      @N_Instance_Values

Then I assign my command string, with its three parameters, to the property CommandText of my SQL command:

    my_SQL_INSERT_command.CommandText = my_SQL_command_string

My SQL command has a property Parameters which is an instance of a collection class with a method Add. So, I use the method Add to add a value for parameter

    @Instance_Values_Sum

to the collection:

    my_SQL_INSERT_command.Parameters.Add( "@Instance_Values_Sum", _
      SqlDbType.bigint, 8 ).Value = new_instance_values_sum

I continue with such uses of the method Add and assign values to the other two parameters

      @Instance_ID
      @N_Instance_Values

Eventually I execute the SQL command via:

    my_SQL_INSERT_command.ExecuteNonQuery

For the value I give to parameter

      @Instance_Values_Sum

it is 8 bytes of binary and, thus, could have anything that could be in 8 bytes. So, presumably my SQL query is fully safe from any data at all and also from 'SQL injection'.

Is this what is meant by 'parameterizing' a SQL query?

Nice article. Thanks.

That article is at about the level I need!

Yes, so far I'm trying to stay with the single byte character set. If my business 'goes international', then to convert my code to handle all the world's languages I'll have plenty of money and get consulting from Microsoft and/or hire people!

I never spent much time writing SQL queries, have been away from SQL writing ASP.NET and other work, so am rusty on SQL and ADO.NET. But I just checked some of my old ADO.NET code and see that at times I did 'parameterize' some queries instead of just doing a string 'concatenation' to get the SQL command 'text'.

So, if parameterizing permits sending SQL Server just any garbage data safely, and it SHOULD, then I should be fairly safe just by parameterizing, which is easy enough to do.

Your article also mentioned threats that could come from inside a stored procedure: So far all my ADO.NET SQL work is so simple I've not been tempted to write stored procedures! So I don't have any stored procedures to be threats!

My usage of SQL Server is intended to be dirt simple, almost simple enough for just some key-value store. But I writing for SQL Server anyway expecting that at some point I will be glad I did, e.g., for some off-line analyses.

Thanks for your article and your post here.

Reply on news.ycombinator.com