| Author |
Message
|
| ubbii |
 Posted: Tue May 23, 2006 11:42 pm Post subject: Insert Error in ESQL with single quotes |
 |
|
Newbie
Joined: 23 May 2006
Posts: 4
|
We have such ESQL code in our project:
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
-- CALL CopyEntireMessage();
DECLARE SEQ_NO INTEGER;
DECLARE MSG_NAME CHARACTER;
DECLARE MSG_DESC CHARACTER;
SET SEQ_NO = InputBody.Root.AAA;
SET MSG_NAME = InputBody.Root.BBB;
SET MSG_DESC = InputBody.Root.CCC;
INSERT INTO Database.TestDB (SEQ_NO, MSG_NAME, MSG_DESC) VALUES(SEQ_NO, MSG_NAME, MSG_DESC);
SET OutputRoot.usr.Result = 'Success';
RETURN TRUE;
END;
If CCC contains a single quotes, for example, the value of CCC is 'abc'efg', an error will occur:
[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Unclosed quote before the character string ')'.
What can I do to prevent this error? Thanks. |
|
| Back to top |
|
 |
| pottas |
| Posted: Wed May 24, 2006 1:11 am Post subject: |
|
|
Disciple
Joined: 27 Oct 2005
Posts: 185
Location: South Africa
|
ubbii,
| Quote: |
If CCC contains a single quotes, for example, the value of CCC is 'abc'efg', an error will occur:
[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Unclosed quote before the character string ')'.
|
Quite right. Replace the string 'abc'efg' with 'abc''efg' - this should get rid of the error. If it is incoming data (an InputRoot message that you have no control over) you best bet is to write a function that will either strip those quote out or add another one to it as in the above example.
Hope it helps.
pottas |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed May 24, 2006 1:11 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi ubbii,
You will need to put another single quote before the single quote in the field...
i.e for 'abc'efg' you will have to insert 'abc''efg'
For this you can check the field in which there is a possibility of getting a single quote and before the insert add another quote by finding the position....Use POSITION
Regards. |
|
| Back to top |
|
|
| pottas |
| Posted: Wed May 24, 2006 1:15 am Post subject: |
|
|
Disciple
Joined: 27 Oct 2005
Posts: 185
Location: South Africa
|
elvis_gn...
How's that for perfect timing???  |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed May 24, 2006 1:33 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi pottas,
I coudn't agree more... 
I was planning to post again after having seen our posts at the same time....imagine those being in sync too
Regards. |
|
| Back to top |
|
|
| ubbii |
| Posted: Wed May 24, 2006 2:22 am Post subject: |
|
|
Newbie
Joined: 23 May 2006
Posts: 4
|
| Thanks for your reply. However, there're lot of places that we are using such INSERT function and each function has a lot of parameters. Is there any way that I can fix this problem without adding something like FORMAT_PARAM(param) everywhere. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed May 24, 2006 2:41 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi ubbii,
I think you will simply have to talk to the sender of the data about which possible fields could have this type of bottlenecks...I would expect fields like "Description" to have special characters...These are design considerations.
or u could simply do a REPLACE single-quote with a double single-quote, right at the beginning of the ESQL....if you are confidant enough
Regards. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed May 24, 2006 3:11 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Could he fix the problem passing the fields as parameters to the SQL or if using a SQL passthrough ?
Normally this stuff only happens if you are passing the char field as value and not as parameter... not a good practice anyway as it is subject to SQL injection ...
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| ubbii |
| Posted: Wed May 24, 2006 3:15 am Post subject: |
|
|
Newbie
Joined: 23 May 2006
Posts: 4
|
| elvis_gn wrote: |
Hi ubbii,
I think you will simply have to talk to the sender of the data about which possible fields could have this type of bottlenecks...I would expect fields like "Description" to have special characters...These are design considerations.
or u could simply do a REPLACE single-quote with a double single-quote, right at the beginning of the ESQL....if you are confidant enough
Regards. |
i c. So I guess there's no mechanism to do with such situation automaticly in ESQL. I will check with our customers to see if it's possible to do this manually. Thanks for your help. |
|
| Back to top |
|
|
| ubbii |
| Posted: Wed May 24, 2006 3:19 am Post subject: |
|
|
Newbie
Joined: 23 May 2006
Posts: 4
|
| fjb_saper wrote: |
Could he fix the problem passing the fields as parameters to the SQL or if using a SQL passthrough ?
Normally this stuff only happens if you are passing the char field as value and not as parameter... not a good practice anyway as it is subject to SQL injection ...
Enjoy |
Actually, we can not control how ESQL communicate with the db server. Below is the trace I got from ODBC:
HSTMT 0x006be2d8
UCHAR * 0x006bf0d8 [ -3] "INSERT INTO TestDB(SEQ_NO, MSG_NAME, MSG_DESC) VALUES (12, 'abcde', 'fla'kdsjf')"
SDWORD -3
ppid=12079:pid= 2f3b:1a EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
HSTMT 0x006be2d8
UCHAR * 0x006bf0d8 [ -3] "INSERT INTO TestDB(SEQ_NO, MSG_NAME, MSG_DESC) VALUES (12, 'abcde', 'fla'kdsjf')"
SDWORD -3
ppid=12079:pid= 2f3b:1a ENTER SQLFreeStmt
HSTMT 0x006be2d8
UWORD 0 <SQL_CLOSE>
ppid=12079:pid= 2f3b:1a EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0x006be2d8
UWORD 0 <SQL_CLOSE>
ppid=12079:pid= 2f3b:1a ENTER SQLExecute
HSTMT 0x006be2d8
ppid=12079:pid= 2f3b:1a EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x006be2d8
ppid=12079:pid= 2f3b:1a ENTER SQLError
HENV 0x00000000
HDBC 0x00000000
HSTMT 0x006be2d8
UCHAR * 0xebe0862c (NYI)
SDWORD * 0xebe08628
UCHAR * 0x006bf9c0
SWORD 1024
SWORD * 0xebe08626
ppid=12079:pid= 2f3b:1a ENTER SQLErrorW
HENV 0x00000000
HDBC 0x00000000
HSTMT 0x006be2d8
WCHAR * 0xebe08468 (NYI)
SDWORD * 0xebe08628
WCHAR * 0xebe08268
SWORD 512
SWORD * 0xebe08626
ppid=12079:pid= 2f3b:1a EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0x00000000
HDBC 0x00000000
HSTMT 0x006be2d8
WCHAR * 0xebe08468 (NYI)
SDWORD * 0xebe08628 (102)
WCHAR * 0xebe08268 [ 74] "[DataDirect][ODBC 20101 driver][SQL Server]Incorrect syntax near 'kdsjf'."
SWORD 512
SWORD * 0xebe08626 (74)
ppid=12079:pid= 2f3b:1a EXIT SQLError with return code 0 (SQL_SUCCESS)
HENV 0x00000000
HDBC 0x00000000
HSTMT 0x006be2d8
UCHAR * 0xebe0862c (NYI)
SDWORD * 0xebe08628 (102)
UCHAR * 0x006bf9c0 [ 74] "[DataDirect][ODBC 20101 driver][SQL Server]Incorrect syntax near 'kdsjf'."
SWORD 1024
SWORD * 0xebe08626 (74)
ppid=12079:pid= 2f3b:1a ENTER SQLError
HENV 0x00000000
HDBC 0x00000000
HSTMT 0x006be2d8
UCHAR * 0xebe0862c (NYI)
SDWORD * 0xebe08628
UCHAR * 0x006bf9c0
SWORD 1024
SWORD * 0xebe08626
ppid=12079:pid= 2f3b:1a ENTER SQLErrorW
HENV 0x00000000
HDBC 0x00000000
HSTMT 0x006be2d8
WCHAR * 0xebe08468 (NYI)
SDWORD * 0xebe08628
WCHAR * 0xebe08268
SWORD 512
SWORD * 0xebe08626
ppid=12079:pid= 2f3b:1a EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0x00000000
HDBC 0x00000000
HSTMT 0x006be2d8
WCHAR * 0xebe08468 (NYI)
SDWORD * 0xebe08628 (105)
WCHAR * 0xebe08268 [ 91] "[DataDirect][ODBC 20101 driver][SQL Server]Unclosed quote before the character string ')'."
SWORD 512
SWORD * 0xebe08626 (91)
ppid=12079:pid= 2f3b:1a EXIT SQLError with return code 0 (SQL_SUCCESS)
HENV 0x00000000
HDBC 0x00000000
HSTMT 0x006be2d8
UCHAR * 0xebe0862c (NYI)
SDWORD * 0xebe08628 (105)
UCHAR * 0x006bf9c0 [ 91] "[DataDirect][ODBC 20101 driver][SQL Server]Unclosed quote before the character string ')'."
SWORD 1024
SWORD * 0xebe08626 (91)
I just got confused with this trace. It seems that broker prepared the command using parameters and then run a pure SQL ignore the prepared command. Any idea? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed May 24, 2006 3:26 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
No because the use of parms should have given something like:
prep statement = "insert into DB values(?,?,?)"
Enjoy
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed May 24, 2006 3:32 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi,
| fjb_saper wrote: |
Could he fix the problem passing the fields as parameters to the SQL or if using a SQL passthrough ?
Normally this stuff only happens if you are passing the char field as value and not as parameter... not a good practice anyway as it is subject to SQL injection ...
Enjoy |
I tried doing an insert into the table directly, it did not work...so i guess a passthrough would also not work...
You could do something: by perhaps catching this type of database exception in the ESQL (after a failed insert check the SQLSate etc)...and whichever field has thrown it, can be corrected and another insert can be attempted...
You could also maybe create a stored procedure for this...not very good with that... 
But the most simple would be to talk to the sender and work with him.
Regards. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed May 24, 2006 8:07 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Can you try a PASSTHRU statement of the form suggested above, ie:
PASSTHRU('insert into TestDB (SEQ_NO, MSG_NAME, MSG_DESC) values(?,?,?)', SEQ_NO, MSG_NAME, MSG_DESC);
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| sirsi |
| Posted: Wed May 24, 2006 8:24 am Post subject: |
|
|
Disciple
Joined: 11 Mar 2005
Posts: 177
|
|
| Back to top |
|
|
|
|
