php-mysql数据库撇号和逗号插入

Question

I am to insert 10 field's value in mysql from php code as it is. The problem is that whenever the user inserts apostrophe and comma(',) the query code is disturbed. some functions are there. But is it necessary to parse all field's value from these functions?? would it not be time consuming :P

here is my php code

$rs = mysql_query("
    insert into 
        _{$pid}_item 
    values (
        '$pid',
        '$item_brand',
        '$item_code',
        '$item_name',
        '$item_quantity',
        '$item_mrp',
        '$i‌tem_discount',
        '$item_vat',
        '$item_sat',
        '$item_selling_price',
        '$item_rating',
        '$item‌​_image'
    )
"); 

I am passing the values to these variables..

Answer 1

Try something like mysql_real_escape_string, or if using PDO, use PDO::quote.

And please please please read up on SQL injection attacks. It is not just a matter of getting failed queries, it is a matter of having an attacker get access to your entire database, like all other user's information.

Even better is to use prepared statements. This would look something like this:

<?php
//Use of $pid in the table name is strange here (see comments section) and is
// dangerous unless you're generating it yourself entirely from known information
// sources. Otherwise you definitely need to sanitize it, which I don't think
// prepared statements or quoting can do.
$stmt = $dbh->prepare("
    INSERT INTO 
        :_{$pid}_item
    VALUES (
        :pid,
        :item_brand,
        :item_code,
        :item_name,
        :item_quantity,
        :item_mrp,
        :i‌tem_discount,
        :item_vat,
        :item_sat,
        :item_selling_price,
        :item_rating,
        :item‌​_image)
"); 

$stmt->bindParam(":pid", $pid);
$stmt->bindParam(":item_brand", $item_brand);
$stmt->bindParam(":item_code", $item_code);
//... etc ...
$stmt->execute();

?>