4 ms·

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

by mtogo 15y ago

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.

NY_Entrepreneur 15y ago
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?

by mtogo 15y ago

NY_Entrepreneur 15y ago

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?