<

PAEVISS – Programmers Against Embedded Values In SQL Statements

Published on
9,067 Points
2,467 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
Comment
Author:PaulHews
3 Comments
 
LVL 50

Expert Comment

by:DanRollins
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 ;-)
0
 
LVL 38

Author Comment

by:PaulHews
Thanks Dan, I think you did a good job with the edits.
0
 
LVL 15

Expert Comment

by:JackOfPH
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
If you, like me, have a dislike for using Online Subscription anti-spam services, then this video series is for you. I have an inherent dislike of leaving decisions such as what is and what isn't spamming to other people or services for me and insis…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month