Community Pick: Many members of our community have endorsed this article.

PAEVISS – Programmers Against Embedded Values In SQL Statements

Published:
First there was PAFSO – Programmers Against the File System Object
Then there was PAUES – Programmers Against the Use of the End Statement

Now Experts Exchange brings you PAEVISS - Programmers Against Embedded Values In SQL Statements

With ADO.NET there is sometimes a need to insert, update or query the database using a command object.
Using Connection As SqlConnection = New SqlConnection(ConnectionString)
	Connection.Open()
	Dim CmdText As String = "INSERT INTO [Person] (FirstName, LastName) " & _
                "VALUES ('" & txtFirstname.Text & "', '" & txtLastname.Text & ")"

	Using cmd As SqlCommand = New SqlCommand(CmdText, Connection)
		cmd.ExecuteNonQuery()
	End Using
	Connection.Close()
End Using

Open in new window

While code like this will work sometimes, it isn’t ideal for a number of reasons:

If either of the textbox values contains an apostrophe, then it requires escaping by doubling up.
By entering SQL statements in the textboxes, a user could perform a SQL injection attack.
Values such as numbers and dates will be coerced into strings, which are subject to culture rules, which can cause problems.  For example, there is always much confusion over which date string format is used when sending dates to SQL server using formatted strings.  The US format of MM/dd/yyyy will often cause confusion in environments expecting a little endian date format (dd/MM/yyyy.)
Instead, let's look at how you can use parameterized queries.
Using Connection As SqlConnection = New SqlConnection(ConnectionString)
	Connection.Open()
	Dim CmdText As String = "INSERT INTO [Person] (FirstName, LastName) " & _
		"VALUES (@lastname, @firstname)"

	Using cmd As SqlCommand = New SqlCommand(CmdText, Connection)
		With cmd.Parameters
			.AddWithValue("@lastname", txtLastname.Text)
			.AddWithValue("@firstname", txtFirstname.Text)
		End With
		cmd.ExecuteNonQuery()
	End Using
	Connection.Close()
End Using

Open in new window

In the command text, we’ve added @lastname and @firstname as placeholders or parameters, which then must be added, along with their values, to the parameters collection of the command object.  The "AddWithValue" method is convenient for adding parameters with their values.  .NET 1.x users will not have access to this method.  There is an overload of "Add" that allows you to specify a parameter value, but this was deprecated by the AddWithValue method as explained in the MSDN documentation.

You may notice that this looks similar to the syntax that is used for stored procedures.   In fact, the use of parameters can have an effect at the server level to increase execution plan reuse, and thus boost performance slightly.  (However, if your SQL Server database has forced parameterization, then every query submitted to the database is compiled with parameters.)

There are so many benefits, and so few drawbacks, that I recommend parameterized queries as a best practice.  Avoid the messy, dangerous practice of embedding values into SQL command strings.
6
2,699 Views

Comments (3)

CERTIFIED EXPERT
Author of the Year 2009

Commented:
Great Article.  I voted Yes!

I can't read about SQL Injection without thinking of...

A database guru or geek will,
Say: No DBA elsewhere's my equal.
        I can easily handle
        a "Bobby'; DROP TABLE" vandal,
'cuz I always escape all my SQL!

But parameterizing the input trumps escaping it, every time ;-)

Author

Commented:
Thanks Dan, I think you did a good job with the edits.

Commented:
Thanks, Now, I have a reference in which I can prove to my boss why I do not want to use embedded values in SQL statement.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community