Quick Tip: Passing Variables to SQL Server

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Wow, the week is almost over and I am yet to post something.

This week I was working on a small development project using Visual Basic for Applications (VBA) to execute some commands at the SQL Server level after some user interface events. The code to be executed needed to have values from the windows passed through to the Transact-SQL.  Now most people just write the code and pass through the value of a string field concatenated before and after with single quotes.

sqlstring = "select * from IV00101 where ITEMNMBR = '" & CStr(ItemNumber) & "'"

This is fine most of the time….. but what happens when the string field itself contains a single quote character.

Well this issue can occur in most languages when the string terminating character is included in the string itself and causes an early termination of the string. While the code will probably fail badly, this is also a security risk as it could be exploited to inject SQL commands.

I wrote a couple of Knowledge Base (KB) articles a while back that talked about this issue in relation to Dexterity and how you need to use the SQL_FormatStrings() global function to create the string value to send to SQL.

The SQL_FormatStrings() function adds the single quote delimiters but also will double up any single quotes in the string to ensure the resulting string does not terminate early. For example:

Field = “This is my test”
so
SQL_FormatStrings(Field) = ‘This is my test’

Field = “This is Pat’s test”
so
SQL_FormatStrings(Field) = ‘This is Pat”s test’

Well, while writing this VBA project, I decided I needed a similar function for use with VBA and so I created a SQL_FormatStrings() function. You can add this function to each module as you need it or create a new code module so the single piece of code can be reused.
VBA Code for SQL_FormatStrings()

Option Explicit

Public Function SQL_FormatStrings(IN_String As String) As String
  Dim X As Integer, Y As Integer
  Dim l_String As String

  l_String = Trim(IN_String)
  X = 1
  Y = InStr(X, l_String, "'")
  While Y > 0
    l_String = Left(l_String, Y) + Mid(l_String, Y)
    X = Y + 2
    Y = InStr(X, l_String, "'")
  Wend
  SQL_FormatStrings = "'" & l_String & "'"
End Function

To use the function you just need to add it around your field.  Note you can remove the single quotes from the query as the function adds them for you.

sqlstring = "select * from IV00101 where ITEMNMBR = " & SQL_FormatStrings(CStr(ItemNumber))

Adding the solution suggsted by Jon in the comments using the VBA Replace() function:

sqlstring = "select * from IV00101 where ITEMNMBR = '" & Replace(CStr(ItemNumber),"'","''") & "'"

Potential problem solved!!!
For related posts have a look at

Hope you find this useful.

David

16-Dec-2011: Added method using Replace() function.

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

10 thoughts on “Quick Tip: Passing Variables to SQL Server

  1. Thanks Dave! Another option is to always parameterize your query. That will take care of any special characters in your input string, improve SQL performance, and prevent against SQL injection. (Oh how I hope to never see SQL injection in a GP instance!)
    From an ADODB.Command named cmd:
    cmd.CommandText = “select * from IV00101 where ITEMNMBR = ?”
    cmd.Parameters.Append cmd.CreateParameter(,adVarChar, adParamInput, 31, ItemNumber)
    support.microsoft.com/…/181734

    Like

  2. Why not just use the VBA replace command?
    e.g.
    sqlstring = "select * from IV00101 where ITEMNMBR = " &replace(CStr(ItemNumber), "'", "''")
    Jon.

    Like

  3. Parameterized queries are probably the best approach as Frank suggests.  But Jon's idea of using replace() in VBA is much simpler and is the approach that I use in my own VBA work.

    Like

  4. SQL Server commands at the client level are simply a bad programming practice. I'm old school when it comes to certain things and I believe that the business logic should reside where the data is and this is the reason I would encourage using a stored procedure, rather than formatted SQL commands at the UI level, even for what would appear to be a simple SELECT statement – heck, that's what the Runtime Engine does most of the time with the auto-procedures. For example:
    Set oCmd = CreateObject("ADODB.Command")
    With oCmd
    .ActiveConnection = oCn
      .CommandType = adCmdStoredProc
      .CommandText = "someCustomStoredProc" 'the custom stored proc
    .Parameters.Append .CreateParameter ("@I_vSomeString", adVarchar, adParamOutput, 25)
      .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)
    oCmd.Parameters("("@I_vSomeString").Value = someVBStringVariable
    .Execute
    Result = oCmd.Parameters("@O_iErrorState").Value
    End With
    Set oCmd = Nothing
    CREATE PROCEDURE someCustomStoredProc
    @I_vSomeString VARCHAR(25),
      @O_iErrorState INT
    AS
    SELECT * FROM IV00101 WHERE ITEMDESC = quotename@I_vSomeString , '''');
    GO
    Too much effort for a simple SELECT statement? Maybe. Issues with compact, portable code? Perhaps. But the benefits outweigh the inconveniences.
    Mariano Gomez, MVP

    Like

  5. Hi everyone,
    Great to see the discussion and the different ideas being posted. This proves that there are many different solutions and readers can choose what works best for them. Thanks
    @Frank: Parameters are a good idea, not so sure how well it works when there are multiple pararmeters needed in a single select statement. Also note that the possible security risk is not in GP itself, but in custom code added to GP.
    @Jon: I like the Replace() idea, but you will need to add back the single quotes either side of the variable. It could be used to create a simpler version of SQL_FormatStrings().
    @Mariano: Parameters calling a stored procedure are great but are overkill for a simple select statement on a quick VBA modification.
    David

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.