Trouble with inserting a string containing a single quote

404759

Using php with Oracle

If I do the following two lines before sending my $Query string through the parse function

$name = "Dominick's";
$Query = "INSERT INTO customers (name) values ('$name')";

it gives me the following error:

Warning: Ora_Parse failed (ORA-00917: missing comma -- while processing OCI function OPARSE)

If I try and force the single quote to be surrounded by double quotes and therefore not be confused:

$name = "Dominick's";
Query = "INSERT INTO customers (name) values (\"$name\")";

Trying that yields the following error:

Warning: Ora_Parse failed (ORA-01741: illegal zero-length identifier -- while processing OCI function OPARSE)

Help

Jeff

3047

Singles quotes need to be duplicated for insertion into Oracle. What
about this:

$name = "Dominick's";
$name = ereg_replace("'", "''", $name);
$Query = "INSERT INTO customers (name) values ('$name')"

Or if you want to modify the PHP source, see:
http://www.phpbuilder.com/mail/php-developer-list/199811/0519.php

Using bind variables is a good long term solution. See
http://www.databasejournal.com/features/oracle/article.php/10893_1547531_2

Another reference for background information is http://www.php.net/addslashes

-- CJ

361642

If it is possible (and here it is) you should use str_replace instead of ereg_replace. So we have...

$name = "Dominick's";
$name = str_replace("'", "''", $name);
$Query = "INSERT INTO customers (name) values ('$name')"

But I would prefer this way...

$name = "Dominick's";
$Query = "INSERT INTO customers (name) values ('".addSlashes($name)."')";

3047

If it is possible (and here it is) you should use str_replace instead of ereg_replace

Thanks for the reminder about str_replace().

$Query = "INSERT INTO customers (name) values ('".addSlashes($name)."')";

This gives an invalid Oracle SQL statement, which will generally fail with

  ORA-01756: quoted string not properly terminated

For Oracle, single quotes must be doubled, not escaped with backslash.

Of the solutions to insert the data, I'd prefer using bind variables
since no escaping or quote doubling is needed.

-- CJ

361642

I have been watching the AddSlashes command doubling the single quote (yes I know that it should add a backslash).

3047

Torsten,

Do you have magic_quotes_sybase on?

See http://www.php.net/manual/en/ref.sybase.php#ini.magic-quotes-sybase

-- CJ

361642

I always wondered what this magic quotes thing is good for. Thanks!