<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

PAEVISS – Programmers Against Embedded Values In SQL Statements

Published on
9,120 Points
2,520 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

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Join & Write a Comment

Please check the video also in regards to recovery of deleted emails from office 365 admin center and through the MFCMAPI tool. I have mentioned each and every step with the proper steps that need to be taken care of.
Watch this online video tutorial and learn the best way to reduce Outlook mailbox size using Compact Now feature of Outlook. It removes the deletes item's space from Microsoft Outlook 2016, 2013, and 2010 and compresses the PST file size. This will …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month