Parameterised SQL Queries

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));

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Matt

    SQL Server 2000 has a really nice feature, the ability to pass an XML document/extract as a parameter. I’ve found this to be a really smooth solution in situations where the number of parameters may vary. Check out this extract from a stored proc I wrote recently to get a feel for the level of optimisation that can be achieved (and I’m sure there are even more techniques to take it even further!);

    CREATE PROCEDURE db_user.pr_UpdateTeamSelection
    @xmlPlayers varchar(1050)
    AS
    SET NOCOUNT ON
    DECLARE @iDoc int, @sql nvarchar(1000), @param_defs nvarchar(40)

    CREATE TABLE #NewSelection (TeamMemberID int, PlayerID int, PosID smallint)

    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @xmlPlayers

    SET @sql = N’INSERT INTO #NewSelection SELECT tm_id, ply_id, pos_id FROM OPENXML (@doc_no, @level, 1) WITH (tm_id int, ply_id int, pos_id smallint)’
    SET @param_defs = N’@doc_no int, @level varchar(12)’
    EXECUTE sp_executesql @sql, @param_defs, @doc_no = @iDoc, @level = ‘/ROOT/PLAYER’

    EXECUTE sp_xml_removedocument @iDoc

    – code to manipulate temp table created from XML document passed in as a parameter…

  • Shashank

    Its a good quick reference

  • Ray

    Can you get execution plan when using parameterised queries? I am able to get “showplan_all” work as long as there are no parameters but as soon as i add sqlparameters to it the sqldatareader returns nothing.