CODEWITHSUNDEEP
phpsqlpostgresql
joshua14
joshua14

Reputation: 135

Insert text dynamically with single quotes in PostgreSQL

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.

Upvotes: 1

Views: 1604

Answers (1)

Dekel
Dekel

Reputation: 62556

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') ");
}

Upvotes: 1

Related Questions