<

PAEVISS – Programmers Against Embedded Values In SQL Statements

Published on
9,204 Points
2,604 Views
6 Endorsements
Last Modified:
Approved
Community Pick
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
Author:PaulHews
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free