I am trying to create an SQL statement using user-supplied data. I use code similar to this in C#:
var sql = "INSERT INTO myTable (myField1, myField2) " + "VALUES ('" + someVariable + "', '" + someTextBox.Text + "');"; var cmd = new SqlCommand(sql, myDbConnection); cmd.ExecuteNonQuery();
and this in VB.NET:
Dim sql = "INSERT INTO myTable (myField1, myField2) " & "VALUES ('" & someVariable & "', '" & someTextBox.Text & "');" Dim cmd As New SqlCommand(sql, myDbConnection) cmd.ExecuteNonQuery()
However,
O'Brien
),How do I do it "the right way"?
To do this: Create a select query, and then open the query in Design view. In the Criteria row of the field you want to add a parameter to, type Like "*"&[, the text that you want to use as a prompt, and then ]&"*".
Description. The SQL Server (Transact-SQL) AND condition and OR condition can be combined in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.
The split() method is useful for getting multiple inputs from users. The syntax is given below. The separator parameter breaks the input by the specified separator. By default, whitespace is the specified separator.
Use parameterized SQL.
Examples
(These examples are in C#, see below for the VB.NET version.)
Replace your string concatenations with @...
placeholders and, afterwards, add the values to your SqlCommand. You can choose the name of the placeholders freely, just make sure that they start with the @
sign. Your example would look like this:
var sql = "INSERT INTO myTable (myField1, myField2) " + "VALUES (@someValue, @someOtherValue);"; using (var cmd = new SqlCommand(sql, myDbConnection)) { cmd.Parameters.AddWithValue("@someValue", someVariable); cmd.Parameters.AddWithValue("@someOtherValue", someTextBox.Text); cmd.ExecuteNonQuery(); }
The same pattern is used for other kinds of SQL statements:
var sql = "UPDATE myTable SET myField1 = @newValue WHERE myField2 = @someValue;"; // see above, same as INSERT
or
var sql = "SELECT myField1, myField2 FROM myTable WHERE myField3 = @someValue;"; using (var cmd = new SqlCommand(sql, myDbConnection)) { cmd.Parameters.AddWithValue("@someValue", someVariable); using (var reader = cmd.ExecuteReader()) { ... } // Alternatively: object result = cmd.ExecuteScalar(); // if you are only interested in one value of one row. }
A word of caution: AddWithValue
is a good starting point and works fine in most cases. However, the value you pass in needs to exactly match the data type of the corresponding database field. Otherwise, you might end up in a situation where the conversion prevents your query from using an index. Note that some SQL Server data types, such as char/varchar (without preceding "n") or date do not have a corresponding .NET data type. In those cases, Add
with the correct data type should be used instead.
Why should I do that?
It's more secure: It stops SQL injection. (Bobby Tables won't delete your student records.)
It's easier: No need to fiddle around with single and double quotes or to look up the correct string representation of date literals.
It's more stable: O'Brien
won't crash your application just because he insists on keeping his strange name.
Other database access libraries
If you use an OleDbCommand instead of an SqlCommand (e.g., if you are using an MS Access database), use ?
instead of @...
as the placeholder in the SQL. In that case, the first parameter of AddWithValue
is irrelevant; instead, you need to add the parameters in the correct order. The same is true for OdbcCommand.
Entity Framework also supports parameterized queries.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With