Reading on the forums, this question often arises. How and why should we use parameters in our SQL queries?
First things first. Here’s a parameterised query:
sqlConnection1.Open();
SqlCommand comm = new SqlCommand("select * from foo where id=@fooId",sqlConnection1);
// Create a new parameter with the name of the parameter and in this case the value
SqlParameter idParam = new SqlParameter("@fooId",1004);
comm.Parameters.Add(idParam);
SqlDataReader dr = comm.ExecuteReader();
Notice, in our SQL statement we place parameters prefixed with “@” where we’d normally enter the raw data. A parameter is then created with the name of this subsitution and the value to be entered.
Advantages
Parametered queries offer several advantages over non-parametered queries. Firstly, they help protect against injection attacks as the way in which SQL parameters are passed prevents the use of apostrophes and hyphens that can be used in such attacks. If coupled with stored procedures too, you can also secure the execution of the procedure with permissions, and any input would need to be in the context of the permission holder.
SQL server will also cache the execution plan of a parameterised query. This means the next time you run the same query, the database will already know how to execute your query in cache, speeding up access.
mySQL and Parameters
But not all .NET developers use SQL Server. Some DBMS’s, including mySQL do not use named parameters; that is, you cannot specify a name for the parameters you need to add.
Hence, a question mark “?” is used to specify that a parameter is being used within a SQL statement, and parameters are added in order of appearance within the statement, like so:
OdbcCommand comm = new OdbcCommand("INSERT INTO myUsers(name, address, telephone, postcode) VALUES(?, ?, ?, ?);", odbcConn);
comm.Parameters.Add(new OdbcParameter("", strName));
comm.Parameters.Add(new OdbcParameter("", strAddress));
comm.Parameters.Add(new OdbcParameter("", strTelephone));
comm.Parameters.Add(new OdbcParameter("", strPostcode));
Philip MiseldinePhilip is a Computer Science PhD student at Liverpool John Moores University. He's still not mastered guitar tabs, never finished Mario, and needs a haircut.
