MySQL Server Forum - Single & Double Quotes

Author

Single & Double Quotes

Joel

2005-08-18, 9:38 am

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(s
trCheckForApostrophe
As String) As
String

Dim strSql1 As String
If InStr(strCheckForApo
strophe, "'") = 0 Then
strSql1 = " '" & strCheckForApostroph
e & "'"
Else
strSql1 = " """ & strCheckForApostroph
e & """" & ""
End If
CheckForApostrophe = strSql1

End Function

Joel

2005-08-18, 11:23 am

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(s
trPartId).

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(s
trCheckForApostrophe
As String) As
String

Dim strSql1 As String
If InStr(strCheckForApo
strophe, "'") = 0 Then
strSql1 = " '" & strCheckForApostroph
e & "'"
Else
strSql1 = " """ & strCheckForApostroph
e & """" & ""
End If
CheckForApostrophe = strSql1

End Function

"Norm Cook" < normcookNOSPAM@cable
one.net> wrote in message
news:11g959es12fa87@
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" <joel@ecsnj.com> wrote in message
news:uQMMe.2917$yb.2822@trndny01...
variable[color=darkr
ed]
> it
>
>

"Joel" <joel@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(s
trCheckForApostrophe
As String) As
> String
>
> Dim strSql1 As String
> If InStr(strCheckForApo
strophe, "'") = 0 Then
> strSql1 = " '" & strCheckForApostroph
e & "'"
> Else
> strSql1 = " """ & strCheckForApostroph
e & """" & ""
> End If
> CheckForApostrophe = strSql1
>
> End Function
>
>
>
>

Malcolm Dew-Jones

2005-08-18, 8:23 pm

Joel (joel@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(s
trCheckForApostrophe
As String) As
: String

: Dim strSql1 As String
: If InStr(strCheckForApo
strophe, "'") = 0 Then
: strSql1 = " '" & strCheckForApostroph
e & "'"
: Else
: strSql1 = " """ & strCheckForApostroph
e & """" & ""
: 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.

Bill Karwin

2005-08-18, 8:23 pm

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.

Malcolm Dew-Jones

2005-08-18, 8:23 pm

Bill Karwin (bill@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.

Bill Karwin

2005-08-18, 8:23 pm

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.

Sponsored Links