ODBC: Query is too complex -Reply (Correction)
Xiangyang Sean Liu -- seanliu@oscsystems.com
Friday, March 14, 1997
The SQL string in my following post should be
update T set F = 'V' where ...
where V is itself a string representing the value of the character field F.
Have a single quote in V will make the SQL string invalid.
--- Xiangyang Liu
>>> Xiangyang Sean Liu 03/11/97 12:46pm
>>>
Using CDatabse::ExecuteSQL may cause problems that are hard to
detect. For example, if you want to set field F in table T to value V and
field F is of type SQL_CHAR. So you build a SQL string like the following
'update T set F = V where ... '
and pass it into CDatabse::ExecuteSQL as the argument. If the value V
itself contains a single quote character ('), the call will fail because the
string you passed is invalid. But no exception will be thrown. Even if
you checked the return value of ExecuteSQL and found it wasn't
executed successfully, what can you do about it? Ask your user not to
use certain characters in any character field?
--- Xiangyang Liu
P.S. By the way, I am talking about VC++ 1.52 (working with Sybase
11).
Tony D'Alonzo -- adalonzo@voicenet.com
Tuesday, March 18, 1997
[Mini-digest: 3 responses]
What you need to do is to "quote the quotes" in your strings before passing
them down to the database server. For example, before using the string V
which may contain the value - Sean's String, you need to change it to
Sean''s String. That's a single quote applied to the existing single
quote. I believe Sybase has a function call to do this, or you can just do
it manually.
----------
> From: Sean Liu
> To: mfc-l@netcom.com; mfc-l@netcom.com; seanliu@orbital.oscsystems.com
> Subject: Re: ODBC: Query is too complex -Reply (Correction)
> Date: Friday, March 14, 1997 3:47 PM
>
>
> The SQL string in my following post should be
>
> update T set F = 'V' where ...
>
> where V is itself a string representing the value of the character field
F.
> Have a single quote in V will make the SQL string invalid.
>
> --- Xiangyang Liu
>
>
> >>> Xiangyang Sean Liu 03/11/97 12:46pm
> >>>
>
> Using CDatabse::ExecuteSQL may cause problems that are hard to
> detect. For example, if you want to set field F in table T to value V
and
> field F is of type SQL_CHAR. So you build a SQL string like the
following
>
> 'update T set F = V where ... '
>
> and pass it into CDatabse::ExecuteSQL as the argument. If the value V
> itself contains a single quote character ('), the call will fail because
the
> string you passed is invalid. But no exception will be thrown. Even if
> you checked the return value of ExecuteSQL and found it wasn't
> executed successfully, what can you do about it? Ask your user not to
> use certain characters in any character field?
>
> --- Xiangyang Liu
>
> P.S. By the way, I am talking about VC++ 1.52 (working with Sybase
> 11).
>
>
>
>
-----From: Tim Robinson
At 12:46 PM 3/11/97 -0500, Xiangyang Liu wrote:
>
>Using CDatabse::ExecuteSQL may cause problems that are hard to
>detect. For example, if you want to set field F in table T to value V and
>field F is of type SQL_CHAR. So you build a SQL string like the following
>
> 'update T set F = V where ... '
>
>and pass it into CDatabse::ExecuteSQL as the argument. If the value V
>itself contains a single quote character ('), the call will fail because the
>string you passed is invalid. But no exception will be thrown. Even if
>you checked the return value of ExecuteSQL and found it wasn't
>executed successfully, what can you do about it? Ask your user not to
>use certain characters in any character field?
No, you filter the text the user enters to double up on the single quote
marks. For example:
'John's House' -> 'John''s House'
Then pass that to ExecuteSQL.
| Tim Robinson | Once you let go of your liberty, you |
| http://www.ionet.net/~timtroyr | won't easily get it back. -- Plautus |
-----From: haridev@stanford.com (Haridev VENGATERI - SMG Inc.)
Hi Xiangyang,
The fix for this is very simple. You need to escape the single quotes in
your SQL string.
For eg.
If your SQL string is "Isn't this cool"
your query should look like this:
Update T set F = 'Isn''t this cool'
^^
escape a single quote with an extra quote and this will work...
Hope this helps.
-Haridev
>
>
> The SQL string in my following post should be
>
> update T set F = 'V' where ...
>
> where V is itself a string representing the value of the character field F.
> Have a single quote in V will make the SQL string invalid.
>
> --- Xiangyang Liu
>
>
> >>> Xiangyang Sean Liu 03/11/97 12:46pm
> >>>
>
> Using CDatabse::ExecuteSQL may cause problems that are hard to
> detect. For example, if you want to set field F in table T to value V and
> field F is of type SQL_CHAR. So you build a SQL string like the following
>
> 'update T set F = V where ... '
>
> and pass it into CDatabse::ExecuteSQL as the argument. If the value V
> itself contains a single quote character ('), the call will fail because the
> string you passed is invalid. But no exception will be thrown. Even if
> you checked the return value of ExecuteSQL and found it wasn't
> executed successfully, what can you do about it? Ask your user not to
> use certain characters in any character field?
>
> --- Xiangyang Liu
>
>
Become an MFC-L member
| Вернуться в корень Архива
|