#11 Issue with sql update when single quote in data

[Anonymous]

If you look at the below insert statement, I am escaping the parenthesis with two backslashes to insert the record in the database and that works fine. After that, when I update the same record in that field, when I change the value from 22 to 23 with the two backslashes, it no longer inserts the \( in the db. So what seems to be the issue? Any help would be very much appreciated.
DEVPNCOMS..1> insert into custom.maintenance (KeyField, Device, sModeType, StartTime, EndTime, Flag, EnteredBy, Reason, DevComp, sModeScope, MessageText, LOB) values('Device_serverx_MessageText_test\'s datastore1_\\(22\\)_1353107520_1353108420', ‘serverx’, 'Device', 1353107520, 1353108420, 1, 'gnangia','test','Server','MessageText','test\'s datastore1_\\(22\\)','XXXX');
DEVPNCOMS..1> go
DEVPNCOMS..1> select MessageText from custom.maintenance
DEVPNCOMS..2> go

test's datastore1_\(22\)

DEVPNCOMS..1> update custom.maintenance set MessageText='test\'s datastore1_\\(23\\)'
DEVPNCOMS..2> go
DEVPNCOMS..1> select MessageText from custom.maintenance
DEVPNCOMS..2> go

test's datastore1_(23)

I did some further testing and found that if I use 4 backslashes before the parenthesis, it inserts a single backslash - same as the initial insert - but this is only true if I have a single quote in the value prior to the parenthesis. If there is no single quote or the single quote is after the parenthesis, I only need two backslashes, which seem to be very inconsistent.

[Martin Wesdorp]

Hi,

What version of sqsh are you using, what platform and what database server (MSSQL, Sybase) and what version of OpenClient or freetds is being used? That might help me in trying to reproduce your findings.
When I try to insert a string 'test\'s something' into a table column (Sybase 15.5 OpenClient 15.5, sqsh 2.1.8), I get errors 102 and 105: "Incorrect syntax near 's'." and "Unclosed quote before the character string ')". That is, the server is not able to cope with extraneous quotes, escaped or not. What happens if you use doublequotes to enclose a string like:

update custom.maintenance set MessageText="test's datastore1_\(23\)"

If you use doublequotes to specify the beginning and end of string, you can just use single quotes inside the string and you do not need to escape anything, just code "test's... \( ...\) ..." in the string, that is if you really want a backslash in front of the parentheses. If you need to escape a quote or a doublequote or a newline, you need two backslashes to do just that, and that probably will only work correctly inside sqsh commands like \echo. I agree that using escape characters is not very trivial. Try to circumvent it as much as possible. See also the section Quoting beginning at line 229 in the man page of sqsh-2.1.8.

HTH,
Martin.

[Martin Wesdorp]

After reading through the question again, I would like to add some additional comments to explain what is happening here.

Sourceforge also interprets backslashes as escape characters so that makes it quite hard in this post to produce the correct amount of backslashes. So I edited the post and changed all double backslashes in !!. So you should read a !! as a double backslash in the remainder of this post.

First please note that:

1) Sqsh does not expand $variables or escape characters in between a single quoted string.
2) Everything in the buffer outside single quoted strings will go through expansion, including double quoted strings, only if $expand is true.
3) By default expansion is on, which can be controlled by the sqsh variable $expand. To turn expansion off use:

1> \set expand=[0|Off|False]

or start sqsh with the parameter -Lexpand=0 on the command line.
4) sqsh use double backslashes as escape sequence and does not understand single backslash escape characters.
5) You can use the -e command line parameter with sqsh to make it echo the command buffer it is sending to the server to stdout. You can also switch the variable $echo on (\set echo=On) just to accomplish the same. This might sometimes explain what is really going on under the hood with buffer expansion and what command is finally sent to the server.

So what happened with your update statement then?

update custom.maintenance set MessageText='test\'s datastore1_!!(23!!)'

Well, just before the SQL buffer is submitted to the server it is expanded by default. During expansion $variable names are substituted and escaped characters are skipped from interpretation (but the escape sequence !! itself is removed). So when sqsh encounters the first quote in the buffer it assumes the beginning of a string that should be skipped from expansion and when it encounters the second quote it assumes end of quoted string and resumes normal expansion again. Sqsh does not understand a single backslash as an escape sequence. So it interprets the \' as end of the single quoted string and starts expanding the remainder of the SQL buffer. Then it finds a !!( sequence which it interprets as an escaped ( so it trims of the !!. The same happens with the !!). Then it finds a quote again and assumes a new beginning of a quoted string. But then finally end of buffer is reached and the buffer

update custom.maintenance set MessageText='test\'s datastore1_(23)'

is sent to the server. It is left to the server to interpret the \' escape sequence. When you leave out the \' from the command, sqsh skips expansion of the whole string 'tests datastore1_!!(23!!)' and your backslashes remain there.

So what can you do to circumvent this problem then? 1) Set expand to off. 2) Use 2 single quotes in sequence to escape a single quote. For example:

'test''s datastore1_!!(23!!)'

would work as well. 3) Code enough backslashes for expansion. A single backslash will not be expanded, Two backslashes escape the third character but will be removed from the buffer themselves. So whatever follows a !! remains there. So for example !!! will result in !, but !!!! will result in !! because the first two backslashes escape the third backslash and the fourth backslash will be just interpreted as is. However 5 backslashes just result in one, the first three produce that one backslash and the last two will just be trimmed off. To demonstrate this look at the following SQL and the output it produces: ( so a ! is actually a \ )

1> select "one" ="!"
2> select "two" ="!!"
3> select "three"="!!!"
4> select "four" ="!!!!"
5> select "five" ="!!!!!"
6> select "six" ="!!!!!!"
7> select "seven"="!!!!!!!"
8> go -f -m vertical
one: !
two:
three: !
four: !!
five: !
six: !!
seven: !!!

But this SQL was sent to a Sybase ASE server which does not interpret escape characters. When you connect to a MSSQL server that does interpret \ escape characters, this might complicate things even more. Your initial insert statement for example contains many single quotes, two of them escaped, which led to the situation, according to sqsh, that !!( and !!) was inside single quotes and did not get expanded by sqsh and the string was correctly sent to the server. The server then inserted \( in the database as it interpreted the single backslash escape character to escape a backslash (!!). So probably just a coincidence that the insert worked fine here.

Hope this clarifies some of the internal sqsh behaviour with expansion.

Cheers,
Martin.