By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,460 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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" <no************@cableone.net> 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" <jo**@ecsnj.com> 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" <jo**@ecsnj.com> 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.