0

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.

Community
  • 1
  • 1
Bentley
  • 3
  • 5
  • SQL Server, updated. :) – Bentley Jul 30 '15 at 20:06
  • 1
    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. http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – Sean Lange Jul 30 '15 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 Jul 30 '15 at 20:10
  • 2
    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 Jul 30 '15 at 20:14
  • And I'm trying to pass the value of the SQL String. How is it not a value? – Bentley Jul 30 '15 at 20:15
  • The approved answer at the link you referenced is a very good example of how to do this. – Sean Lange Jul 30 '15 at 20:16
  • You are passing an entire statement, not a value. – Sean Lange Jul 30 '15 at 20:16
  • 1
    If you will post your actual statement I will help you turn it into a parameterized database call. – Sean Lange Jul 30 '15 at 20:17
  • SQL = "LoginPassword 521, 'Lnsu8wnw+2fa\sdJW'" – Bentley Jul 30 '15 at 20:23
  • I take it you have a stored procedure named LoginPassword and you are trying to pass it values of 521 and 'Lnsu8wnw+2fa\sdJW'? – Sean Lange Jul 30 '15 at 20:27
  • 100% correct. The userID is the first number, and the 'crazyStuffHere' is an encrypted password. – Bentley Jul 30 '15 at 20:27
  • Double kudos for storing encrypted passwords!!! – Sean Lange Jul 30 '15 at 20:34

1 Answers1

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

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • 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 Jul 30 '15 at 20:37
  • Maybe a different Stored Proc or an Insert? – Bentley Jul 30 '15 at 20:38
  • 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. – Sean Lange Jul 30 '15 at 20:38
  • 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. – Bentley Jul 30 '15 at 20:43
  • Or just don't use that generic class and do your own like this so it will be properly parameterized. :) – Sean Lange Jul 30 '15 at 20:44
  • I'll look into it. No promises though. :) – Bentley Jul 30 '15 at 20:53
  • One last thought...see if you can extend the existing class so that is can handle parameters. Shouldn't be a big deal. Just expose a new property which is a generic collection of parameters. Also you will need to expose the commandtype of your command object in your class so you can switch back and forth between command types. – Sean Lange Jul 30 '15 at 21:16
  • I think that is the way we'll handle it. Thank you again for everything Sean! – Bentley Jul 30 '15 at 22:01