- DepsonQ Path -

Use Parameters in SQL command

When you are trying to work with data, you probably need to filter the results based on certain criteria. Normally, user will input the specific value and it will be used for the SQL query. The SQL query formed will be assigned to an instance of SQL Command object for further processing. Hence, the SQL query can be built dynamically and pass to a SQL Command object. Just take a look at this example, where a user would like to filter the customers by the first name.

SqlCommand queryCmd = new SqlCommand("Select * from tblCustomers where 

first_name = '" + userFirstName+ "'";", sqlConnection);

In fact, this is not an ideal way to build a query. The input variable, userFirstName, is normally retrieved from the user input, such as TextBox control. If any string is placed into the input control, it will be added into the SQL string. This might create the interest of hacker to put in malicious code and control on your computer. Besides that, SQL injection can be performed easily in such situations, visit here for more information about SQL Injections.

Therefore, you can try to use parameters in your SQL command instead of building the query dynamically. The data placed in the parameter will be treated as field data and not part of the SQL statement. This will help to build a much more secure application. The process of using SQL Parameters will be illustrated as follows:

1. Construct a SQL Command object with command string and parameters.
2. Declare a SQL Parameter object, with proper values assigned.
3. Set the SQL Parameter object to the SQL Command’s Parameter property.

Code Snippet:

SqlCommand queryCmd = new SqlCommand(”Select * from tblCustomers where first_name = @userFirstName;”, sqlConnection);

SqlParameter firstNameParam = new SqlParameter("@userFirstName",

userFirstName");

queryCmd.Parameters.Add(firstNameParam);

Finally, you are done with it. When the SQL Command executes, the particular parameters will be replaced by the specific SQL Parameter object. So, try to apply parameters in building your SQL query for security reason. You can refer to the MSDN Library for more information about SQL Parameter. Please correct me if I made any mistakes here. 

** The code is written in C#.