Quick Tip: Passing Variables to SQL Server
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.
- How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity (KB 910129)
- How to pass dates and times to SQL Server from Dexterity in Microsoft Dynamics GP (KB 929786)
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
- Microsoft Dynamics GP and Illegal Characters Part 1
- Microsoft Dynamics GP and Illegal Characters Part 2
Hope you find this useful.
David
16-Dec-2011: Added method using Replace() function.