Use of reserved word in the value of a INSERT
User reported INSERT statements failing when field values contained the word SELECT. After testing, Art Kagel confirmed the statements work fine in dbaccess and Java ODBC clients. Daniel traced the issue to the PHP PEAR::DB library he was using to connect to IDS 11.50 via esql/c. Jonathan suggested using parameterized queries/placeholders if the library supports them to avoid the problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I recently came across quite an interesting situation whereby an insert statement was failing. After much debugging I found that it was due to the word SELECT within the value of one of the fields. I have tried all the usual methods of delimiting (double-quotes etc) but to no avail. Besides, I would rather not interfere with the data where I can help it. Has anyone out there experienced this issue as well, or have any ideas on how I can overcome it?
ie. INSERT INTO foo (bar) values ('MY SELECT VALUE')
AFAIK there is nothing that would prevent one from inserting the string
'SELECT' into a char/varchar/lvarchar column. Testing...:
> create table string( one char(100));
Table created.
> insert into string values ('SELECT');
1 row(s) inserted.
Yup. works for me. How are you trying to perform the insert? What host
environment (dbaccess, esql/c, esql/cobol, dbaccess LOAD, dbload, something
else)?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Aug 22, 2012 at 8:56 PM, DANIEL KIRTON <phpking@gmail.com> wrote:
> I recently came across quite an interesting situation whereby an insert
> statement was failing. After much debugging I found that it was due to the
> word SELECT within the value of one of the fields. I have tried all the
> usual
> methods of delimiting (double-quotes etc) but to no avail. Besides, I would
> rather not interfere with the data where I can help it.
>
> Has anyone out there experienced this issue as well, or have any ideas on
> how
> I can overcome it?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae93409030ea43804c7e4af09
That works for me as well. Wit and without the column reference:
> insert into string values ('MY SELECT VALUE');
1 row(s) inserted.
> insert into string(one) values ('MY SELECT VALUE');
1 row(s) inserted.
>
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Aug 22, 2012 at 8:58 PM, DANIEL KIRTON <phpking@gmail.com> wrote:
> ie. INSERT INTO foo (bar) values ('MY SELECT VALUE')
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340c3bc4e5ba04c7e4b8bb
hrmmm.. Thanks Art. Your absolutely right - executing the query via dbaccess
or even the java odbc client I use returns successfully. I believe it may be a
deficiency with the PHP libraries im using.
I'm connecting through esql/c to IDS 11.50, via PEAR::DB libraries available
in PHP 5.0.1.
On Wed, Aug 22, 2012 at 6:29 PM, DANIEL KIRTON <phpking@gmail.com> wrote:
> hrmmm.. Thanks Art. Your absolutely right - executing the query via
> dbaccess> or even the java odbc client I use returns successfully. I believe it may
> be a
> deficiency with the PHP libraries im using.
>
> I'm connecting through esql/c to IDS 11.50, via PEAR::DB libraries
> available
> in PHP 5.0.1.
>
Does that library support placeholders for values? If yes, use them.
If no, when you create the SQL statement, you will have to manually insert
the quotes around string values.
Beware of SQL Injection http://xkcd.com/327
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--bcaec54fb7fcd79aab04c7f05c23