How to insert string with single quote in Mysql & Oracle database?

Question

I am facing issue in inserting single quoted value (say Product Name: xyz80'). So how can I insert such data into mysql & oracle database. With double quote, it's working fine. eg: xyz90"

My script:

$query2 = "SELECT sfoi.name, sfoi.sku, sfoi.qty_ordered
FROM  sales_flat_order sfo
JOIN sales_flat_order_item sfoi
ON sfoi.order_id = sfo.entity_id
WHERE sfo.increment_id = 100000473";
$result_query2 = mysql_query($query2);
while($row = mysql_fetch_array($result_query2))
{
     $row["name"] = mysql_real_escape_string($row["name"]);
    // $row["name"] = html_entity_decode($row["name"]);
      $result_str_product .= "('". $row["name"] . "',". "'" . $row["sku"] . "'," . "'" . $row["qty_ordered"]),";
}

I tried using both mysql_real_escape_string() and html_entity_decode(), still getting error. Here $row[name] is fetching value which is like xyz80', pqr75' etc. As I am inserting these values through PHP, unable to get where exactly error is occurring.

I am facing similar problem with Oracle db also. In Oracle , I tried this: "'". $row["name"] . "''," using '' at the end.

HOW TO insert special characters in Oracle dataabse?

`mysql_real_escape_string` should do the trick. Where is the rest of `$result_str_product` set and used? What is the exact error message you're getting? — Travesty3, Mar 26 '12 at 16:59
Is $result_str_product a string where you are building up an insert statement? If so, you should really prepare the query with placeholders (aka bind variables). That will take care of the quoting problem and for Oracle it will be faster too. — Stephen ODonnell, Mar 26 '12 at 19:56
possible duplicate of [How to handle a single quote in Oracle SQL](http://stackoverflow.com/questions/2875257/how-to-handle-a-single-quote-in-oracle-sql) — LittleBobbyTables - Au Revoir, Apr 30 '15 at 14:18

boisvert · Answer 1 · 2012-03-26T17:09:47.397

prepare the data by replacing one ' with two '', before composing the query:

while($row = mysql_fetch_array($result_query2)) {
  $n = mysql_real_escape_string($row["name"]);
  $s = mysql_real_escape_string($row["sku"]);
  $q = mysql_real_escape_string($row["sku"]);
 // $n = html_entity_decode($row["qty_ordered"]);
  $result_str_product .= "('$n','$s','$q'),";
}

// remember_to_remove_final_stray_comma($result_str_product);

print( $result_str_product ); // just to see what's been made

score 3 · Accepted Answer · answered Mar 26 '12 at 22:18

For Oracle, you could replace the single quotes with two single quotes when you query:

$query2 = "SELECT REPLACE(sfoi.name,'''','''''') name, sfoi.sku, sfoi.qty_ordered 
             FROM  sales_flat_order sfo JOIN sales_flat_order_item sfoi 
                                          ON sfoi.order_id = sfo.entity_id
            WHERE sfo.increment_id = 100000473";

Then the rest of your code should work as is.

In Oracle, two consecutive single quotes represent a single quote in a string literal.

score 1 · Answer 3 · answered Mar 26 '12 at 22:28

1

Oracle now have the q function used to escape strings http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#sthref373

select q'my 'quoted text' ' from dual

this solution is nice cause you don't have to have a bunch of nested quotes

answered Mar 26 '12 at 22:28

rlobban

343
1
7

I hate when some editors fail to color syntax correctly with this solution :) – Jokke Heikkilä Mar 27 '12 at 08:12
agreed, pl/sql developer from allroundautomations (no affiliations with them, just use their product) does an ok job of color coding it – rlobban Mar 27 '12 at 16:28
So does Toad newest version but e.g. Notepad++ doesn't get it – Jokke Heikkilä Mar 28 '12 at 07:41

How to insert string with single quote in Mysql & Oracle database?

3 Answers3