Forum Stats

  • 3,838,625 Users
  • 2,262,385 Discussions
  • 7,900,710 Comments

Discussions

Categories

Trouble with inserting a string containing a single quote

404759
404759 Member Posts: 1
edited Oct 8, 2003 5:00AM in PHP
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
· Share on TwitterShare on Facebook

Comments

  • 3047
    3047 Member Posts: 307

    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

    · Share on TwitterShare on Facebook
  • 361642
    361642 Member Posts: 219
    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)."')";

    · Share on TwitterShare on Facebook
  • 3047
    3047 Member Posts: 307
    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
    · Share on TwitterShare on Facebook
  • 361642
    361642 Member Posts: 219
    I have been watching the AddSlashes command doubling the single quote (yes I know that it should add a backslash).
    · Share on TwitterShare on Facebook
  • 3047
    3047 Member Posts: 307
    Torsten,

    Do you have magic_quotes_sybase on?

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

    -- CJ
    · Share on TwitterShare on Facebook
  • 361642
    361642 Member Posts: 219
    I always wondered what this magic quotes thing is good for. Thanks!
    · Share on TwitterShare on Facebook
This discussion has been closed.