434,882 Members | 2,460 Online
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

# Single & Double Quotes

 P: n/a Hi, I incorporated a function in my code that whenever I use a string variable in an sql statement if the string contains a single quote it will encase it in double quotes else single quotes. Queston: How do you handle a string that contains both single & double quotes (i.e. 12'X7") Here's the function: Public Function CheckForApostrophe(strCheckForApostrophe As String) As String Dim strSql1 As String If InStr(strCheckForApostrophe, "'") = 0 Then strSql1 = " '" & strCheckForApostrophe & "'" Else strSql1 = " """ & strCheckForApostrophe & """" & "" End If CheckForApostrophe = strSql1 End Function Aug 18 '05 #1
5 Replies

 P: n/a Let me clarify: If a string contains both a single & double quote within. How do I write an Sql statement? Here's an example: strPartId="Pipe12' Length" If I write the Sql statement like this: strSql="Select * From Invt Where Part_Id = '" & strPartId & "'" in this instance since strPartId contanis an ' it will bomb. As a result I wrote the function CheckForApostrophe to encase it with double quotes so my Sql statement would read: strSql="Select * From Invt Where Part_Id = " & CheckForApostrophe(strPartId). My problem is what if the value of strPartId="Pipe12'X6" Length And Width" What do you do then? Here's the function: Public Function CheckForApostrophe(strCheckForApostrophe As String) As String Dim strSql1 As String If InStr(strCheckForApostrophe, "'") = 0 Then strSql1 = " '" & strCheckForApostrophe & "'" Else strSql1 = " """ & strCheckForApostrophe & """" & "" End If CheckForApostrophe = strSql1 End Function "Norm Cook" wrote in message news:11************@corp.supernews.com... You're only looking for strings that start and end with the single quote, right? Consider this: Private Function DblQSql(ByVal sql As String) As String Dim sArr() As String Dim i As Long sArr = Split(sql) 'split on spaces For i = 0 To UBound(sArr) If Left\$(sArr(i), 1) = "'" And Right\$(sArr(i), 1) = "'" Then sArr(i) = Chr\$(34) & Mid\$(sArr(i), 2, Len(sArr(i)) - 2) & Chr\$(34) End If Next DblQSql = Join(sArr) End Function "Joel" wrote in message news:uQMMe.2917\$yb.2822@trndny01... Hi, I incorporated a function in my code that whenever I use a string variable in an sql statement if the string contains a single quote it will encase it in double quotes else single quotes. Queston: How do you handle a string with contains both single & double quotes (i.e. 12'X7") Here's the function: Public Function CheckForApostrophe(strCheckForApostrophe As String) As String Dim strSql1 As String If InStr(strCheckForApostrophe, "'") = 0 Then strSql1 = " '" & strCheckForApostrophe & "'" Else strSql1 = " """ & strCheckForApostrophe & """" & "" End If CheckForApostrophe = strSql1 End Function "Joel" wrote in message news:SI%Me.4181\$yb.2508@trndny01... Hi, I incorporated a function in my code that whenever I use a string variable in an sql statement if the string contains a single quote it will encase it in double quotes else single quotes. Queston: How do you handle a string that contains both single & double quotes (i.e. 12'X7") Here's the function: Public Function CheckForApostrophe(strCheckForApostrophe As String) As String Dim strSql1 As String If InStr(strCheckForApostrophe, "'") = 0 Then strSql1 = " '" & strCheckForApostrophe & "'" Else strSql1 = " """ & strCheckForApostrophe & """" & "" End If CheckForApostrophe = strSql1 End Function Aug 18 '05 #2

 P: n/a Joel (jo**@ecsnj.com) wrote: : Hi, : I incorporated a function in my code that whenever I use a string variable : in an sql statement if the string contains a single quote it will encase it : in double quotes else single quotes. : Queston: How do you handle a string that contains both single & double : quotes (i.e. 12'X7") : Here's the function: : Public Function CheckForApostrophe(strCheckForApostrophe As String) As : String : Dim strSql1 As String : If InStr(strCheckForApostrophe, "'") = 0 Then : strSql1 = " '" & strCheckForApostrophe & "'" : Else : strSql1 = " """ & strCheckForApostrophe & """" & "" : End If : CheckForApostrophe = strSql1 : End Function Are you sure that your database interface does not already include a function to do this? E.g. in php you could use mysql_escape_string() and in perl you could use DBI::->quote() Anyway, this is the wrong approach. MySql allows you to escape characters in strings. google mysql escape for details. Basically you need to put an extra \ before various special characters. so if the _contents_ of the string is this has ' and also " in it then the _contents_ of the escaped string would be this has \' and also \" in it That escaped string is something that you can put in a mysql sql statement without worrying about which outer quotes you use select * from t where x = 'we\'ll be \"quoting\" that' How you get all that correctly into a string using your language is up to you. -- This space not for rent. Aug 18 '05 #3

 P: n/a Joel wrote: Queston: How do you handle a string that contains both single & double quotes (i.e. 12'X7") Malcolm suggested using some API support to prepare your string with proper escaping. It looks like you're using Microsoft VB, or something else Microsoft-ish. Another strategy is to use query parameters. Where you would put the string in your SQL statement, instead put a ? symbol, with no quotes, no escaping or backslashes or anything. Most database API's should have some support for this. In VB, use the cmd.Parameters.Append function to supply values to the SQL in place of the ? placeholder. The advantage is that you can have any special characters you want, and you don't have to escape them to accommodate SQL syntax. It's safe to do so because it's unambiguous to the SQL parser that the value you're supplying is a single constant value, no matter what quotes or other special characters are in it. Here are a couple of web pages with examples in VB. Search for usage of the ? character in the code examples. http://support.microsoft.com/kb/q245179/ http://www.vbwm.com/forums/topic.asp?TOPIC_ID=3876 Regards, Bill K. Aug 18 '05 #4

 P: n/a Bill Karwin (bi**@karwin.com) wrote: : Joel wrote: : > Queston: How do you handle a string that contains both single & double : > quotes (i.e. 12'X7") : Malcolm suggested using some API support to prepare your string with : proper escaping. It looks like you're using Microsoft VB, or something : else Microsoft-ish. : Another strategy is to use query parameters. Where you would put the : string in your SQL statement, instead put a ? symbol, with no quotes, no : escaping or backslashes or anything. Most database API's should have : some support for this. That is the best way, but MySql doesn't use bind variables (or didn't, I might be out of date) so I assumed it wasn't available. However it could well be that the VB sql functions support this feature locally for any database, in which case it should be used for sure. : In VB, use the cmd.Parameters.Append function to supply values to the : SQL in place of the ? placeholder. The advantage is that you can have : any special characters you want, and you don't have to escape them to : accommodate SQL syntax. It's safe to do so because it's unambiguous to : the SQL parser that the value you're supplying is a single constant : value, no matter what quotes or other special characters are in it. : Here are a couple of web pages with examples in VB. Search for usage of : the ? character in the code examples. : http://support.microsoft.com/kb/q245179/ : http://www.vbwm.com/forums/topic.asp?TOPIC_ID=3876 : Regards, : Bill K. -- This space not for rent. Aug 18 '05 #5

 P: n/a Malcolm Dew-Jones wrote: That is the best way, but MySql doesn't use bind variables (or didn't, I might be out of date) so I assumed it wasn't available. I regularly use bind variables in MySQL 4.0 and 4.1. Regards, Bill K. Aug 18 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.