php-mysql database apostrophe and comma insertion

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..

It would be a lot easier if you provide some code to look at instead of forcing us to hire fortunetellers to gues where your mistake is ;) — Hristo Valkanov, Aug 07 '13 at 16:43
Yes, use [prepared statements](http://php.net/pdo) or [input sanitation](http://bit.ly/15O36d1) to correct for that issue. Great software is time consuming. :-) — phpisuber01, Aug 07 '13 at 16:45
@HristoValkanov here is my php code...$rs=mysql_query("insert into _{$pid}_item values ('$pid','$item_brand','$item_code','$item_name','$item_quantity','$item_mrp','$item_discount','$item_vat','$item_sat','$item_selling_price','$item_rating','$item_image')"); I am passing the values to these variables.. — Rahul Rastogi, Aug 07 '13 at 16:48
If you're able to edit your post (not sure if your reputation is too low), please do so and put your code in there:it is unreadible in the comments. — brianmearns, Aug 07 '13 at 16:52
You should use mysqli http://php.net/manual/en/book.mysqli.php or PDO http://php.net/manual/en/book.pdo.php — Logan Murphy, Aug 07 '13 at 16:56
possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — deceze, Dec 13 '13 at 14:01

brianmearns · Accepted Answer · 2013-08-07T17:03:22.123

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();

?>

Using `$pid` in the table name seems like pretty strange database architecture. Do you have separate identical tables for each possible vlaue of `pid`? Why not put them all in one table, since you have `pid` stored in the row anyway. This also means you don't need to worry about sanitizing the table name. — brianmearns, Aug 07 '13 at 17:05

score 2 · Answer 2 · edited May 23 '17 at 12:23

2

The best complete explanation about your problem can be found here.

As you probably noticed, if someone is able to input anything and crash your system, your code is not correctly implemented.

In the article above is explained the best way to avoid this happening. Have a nice time reading the explanations and choosing the method that most fits your case. :)

edited May 23 '17 at 12:23

Community

1
1

answered Aug 07 '13 at 16:53

Hristo Valkanov

1,689
22
33

score -2 · Answer 3 · answered Aug 07 '13 at 16:48

-2

    $query = str_replace("\'","''", $query);
    $query = stripslashes($query);

I have been using these two babies for similar situation. I haven't heard a complain yet. Give it a try. Or play with it.

answered Aug 07 '13 at 16:48

Rabin

418
3
13

Of course not, why would hackers complain after they got a dump of your database =). This is not secure enough against SQL injection attacks. On the PHP documentation page for `addslashes()`: "To escape database parameters, DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_literal(), pg_escape_string() for PostgreSQL) should be used for security reasons" – brianmearns Aug 07 '13 at 16:57
@user2155922: If you're satisfied with the answer I provided above, feel free to click the check mark image next to it to mark it as the accepted answer. – brianmearns Aug 07 '13 at 17:44

score -2 · Answer 4 · answered Aug 07 '13 at 16:48

-2

Use addslashes() php function.

http://php.net/manual/en/function.addslashes.php

It is not as time consuming as you may think. Unnoticeable.

answered Aug 07 '13 at 16:48

Sachem

481
1
4
13

This is not secure enough for SQL queries. From the page you linked: "To escape database parameters, DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_literal(), pg_escape_string() for PostgreSQL) should be used for security reasons" – brianmearns Aug 07 '13 at 16:55

score -2 · Answer 5 · answered Dec 13 '13 at 13:41

Sometimes you need to check your header.

This doesn't accept apostrophe:

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

For you to work well with apostrophe, it's good if you'll just be using this in your header:

<meta http-equiv="content-type" content="text/html; charset=iso-8859-1"/>

php-mysql database apostrophe and comma insertion

5 Answers5