Using SQL Parameters for ''

Question

First time poster - medium length reader. I'm an entry level programmer, currently working on passing a SQL Stored Procedure some information that might contain a single quote (').

In the past, we've attempted to just use a .Replace("'","''") when passing this information, but recently, we've run into some issues with returning data and having the set changes and replaces in about 20 places (corporate, woo!).

I've been looking at using SQL Parameters to not have to worry about these buggers: ', but cannot see/understand the difference in my below code. The first block was the original working version. The second is my attempt at introducing @paras.

SQL is being passed through ByVal as a String

Previous Code:

Dim dbConnection As New SqlConnection(ConnectionString)
Dim dbCommand As New SqlCommand(SQL, dbConnection)
MsgBox(dbCommand.CommandText.ToString) //Returns proper procedure/paras

dbCommand.CommandTimeout = CommandTimeout
dbConnection.Open()
dbCommand.ExecuteNonQuery()

Code with SQL Parameters:

Dim dbConnection As New SqlConnection(ConnectionString)
Dim dbCommand As New SqlCommand("@SQL", dbConnection)
dbCommand.Parameters.Add("@SQL", SqlDbType.VarChar).Value = SQL
MsgBox(dbCommand.CommandText.ToString) //Returns "@SQL"

dbCommand.CommandTimeout = CommandTimeout
dbConnection.Open()
dbCommand.ExecuteNonQuery()

I feel the second block should be returning the same information. A MsgBox from the first block will return the proper SQL. The second however, just returns "@SQL", not the SQL value it seems to assign.

Is there a special way of refreshing the SQL Command? Am I unable to only declare @SQL and replace it later?

Took a peek around MSDN as well as quite a few searches, leading me here already, with no luck.

Building a string and trying to pass that as a parameter isn't going to work. That is not at all how parameters work. Also, you should take a look at this article. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — Sean Lange
– Sean Lange, Commented Jul 30, 2015 at 20:07
Alright, I've made the change on AddWithValue. Thanks for the information. How about a different way to go about it? "That won't work" isn't really helping here... — Bentley
– Bentley, Commented Jul 30, 2015 at 20:10
Parameters are used to pass a value, you are trying to use it to pass in entire sql string. The main benefit of parameters is to prevent sql injection by parameterizing the values. — Sean Lange
– Sean Lange, Commented Jul 30, 2015 at 20:14
If you will post your actual statement I will help you turn it into a parameterized database call. — Sean Lange
– Sean Lange, Commented Jul 30, 2015 at 20:17

Sean Lange · Accepted Answer · 2015-07-30 20:32:59Z

2

Here is how you would make this a parameterized call. Kudos for taking the effort to protect against sql injection!!!

dbCommand.CommandText = "LoginPassword"
dbCommand.CommandType = CommandType.StoredProcedure
dbCommand.Parameters.Add("@userID", SqlDbType.VarChar, 30).Value = userID
dbCommand.Parameters.Add("@Password", SqlDbType.VarChar, 30).Value = password
dbCommand.ExecuteNonQuery()

One thing you need to make sure you do is when you use parameters you should always specify the precision or length. I don't know what yours should be in this case so you will need to adjust as required.

--please forgive me if there is a syntax error. I work with C# but I think I got this correct for vb.net

answered Jul 30, 2015 at 20:32

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Bentley Over a year ago

You're going to hate me for this... what if SQL changes to anything else? This is within a Function called SQLExecute, where we call our connections, etc, and pass whatever our SQL command was.

Bentley Over a year ago

Maybe a different Stored Proc or an Insert?

Sean Lange Over a year ago

Your function is the problem. You shouldn't have a generic thing that can just execute sql. It should be able to handle procedures or ad hoc sql along with parameters or the class just isn't robust enough for a production system.

Bentley Over a year ago

Those kinds of changes would be a bit out of my reach. Pretty new where I'm at, and the system is fairly large. But your answers have certainly helped me overall and I thank you for it. Unfortunately, we've hit a wall.

Sean Lange Over a year ago

Or just don't use that generic class and do your own like this so it will be properly parameterized. :)

|

Collectives™ on Stack Overflow

Using SQL Parameters for ''

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related