Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Trouble with inserting a string containing a single quote
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
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
Comments
-
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
-
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)."')";
-
If it is possible (and here it is) you should use str_replace instead of ereg_replaceThanks 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 -
I have been watching the AddSlashes command doubling the single quote (yes I know that it should add a backslash).
-
Torsten,
Do you have magic_quotes_sybase on?
See http://www.php.net/manual/en/ref.sybase.php#ini.magic-quotes-sybase
-- CJ -
I always wondered what this magic quotes thing is good for. Thanks!
This discussion has been closed.