Insert text dynamically with single quotes in PostgreSQL

Question

I have a problem with inserting data text dynamically from one table to another table which can have a single quotes (').

The link here and here cannot be used in my case because the the data is dynamic (from table).

Here's my code in php:

...
//select from master table with some condition inputted by user
$sql = "SELECT prod_name, prod_price FROM master_prod WHERE cat_prod ='$category'";

//get all data and insert to another table
$rs5 = $db->GetAll($sql);
foreach ($rs5 as $row) {
    $rs = $db->Execute("INSERT INTO trans_temp (name, price) VALUES ('$row[prod_name]','$row[prod_price ]') ");
}
...

My problem is, the prod_name data can have single quotes ('), and when it happens, it won't inserted to table trans_temp because the INSERT code have single quotes ('$row[prod_name]').

Ex.: INSERT INTO trans_temp (name, price) VALUES ('ICE BON BON 5'S 80GR (1C=24)','130')

Can someone help me? Thank you for any answer.

Dekel · Accepted Answer · 2016-12-28 02:54:24Z

1

PDO is probably much better solution, but you can also use '' for escaping:

$rs5 = $db->GetAll($sql);
foreach ($rs5 as $row) {
    $t1 = str_replace("'", "''", $row['prod_name']);
    $t2 = str_replace("'", "''", $row['prod_price']);
    $rs = $db->Execute("INSERT INTO trans_temp (name, price) VALUES ('$t1','$t2') ");
}

answered Dec 28, 2016 at 2:54

Dekel

62.9k12 gold badges109 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert text dynamically with single quotes in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related