Before Step 4: Adding Parameters to SQL Commands
A very common scenario with SQL commands is that you do not know at design time which values you will need. Consider the following case: you wish to get a list of all orders made in the restaurant to table number 4. The proper SQL command is:
SELECT * FROM t_orders WHERE TableNum = 4
Dangerous use of SQL Command Strings
But if you do not know orders from which table you will have to display? Assume the user enters the number at run time. And say you stored it in a variable called strTableNumber. Then you can write something like:
|
|
' ---- WRONG WAY --- Command.CommandText = "SELECT * FROM t_orders WHERE TableNum=" & strTableNumber 'WRONG WAY! Reader.Value = Command.ExecuteReader |
|
The code above will work fine if you entered a number to the strTableNumber variable. But this is very dangerous to use this code in a "real world" application. This code is exposed to "SQL injection" - the most common way to destroy your database by inserting malicious script into the data input. This method takes advantage of the fact the charachter ";" is used in SQL to separate two adjacent statements. So if, as a response to your request for input, the user entered the string "4; DELETE FROM t_order", all records in the t_orders table will be deleted.
In order to protect against this kind of violence, SQL commands use parameters to indicate data that is not known at development time.
Advantages of parameters
Protect against SQL injection.
Faster then concatenated string query (significantly faster).
Eliminate the need to use escape characters for special characters.
Using parameters in an SQL statement
If an SQL statement contains a word preceded by the character "@", the word is interpreted as a parameter:
|
|
Command.CommandText =
"INSERT INTO [table1] (col1, col2) VALUES (@ID, @Name)"
|
|
In this code, the values that will be inserted into the tables are the values stored in the parameters named ID and Name. You declare the parameters using the Command object's AddParameter Sub, and you assign a value to it using the SetParameter Sub:
|
|
Command.AddParameter("ID")
Command.AddParameter("Name")
Command.SetParameter("ID","strNewID")
Command.SetParameter("Name","strNewName")
Command.CommandText = "INSERT INTO [table1] (col1, col2) VALUES (@ID, @Name)" |
|
Note: you must NOT add the "@" character when calling the SetParameter sub. If you wrote something like the following code, an error message will pop, indicating line 4 as the cause to the error. This will not help you much (see common errors) in figuring out you should not have added the "@" at lines 1 and 2.
|
1 2 3 4 |
Command.AddParameter("@ID")
Command.SetParameter("@ID","strNewID")
Command.CommandText = "INSERT INTO [table1] (col1) VALUES (@ID)" Command.ExecuteNonQuery |
|
Next page: Step 4: Implementing the Business Layer