PHP updating mysql issue

[JamesM]

If I am using the following php to update the table:-

$q = "UPDATE results SET hello = '$_POST[hello]' WHERE id = '1'";

and if $_POST[hello] equals:-

hello i'm a cool dude

it would create an error as follows:-

$q = "UPDATE results SET hello = 'hello i'm a cool dude' WHERE id = '1'";

You can see there are too many ' in that query, how would I go about making the query work even if the ' is still there?

 

[Teaf]

I always use a custom character conversion function which I wrap around every type of information that is going into a MySQL database which is plain text. You can see this function below, but I had to show it in a picture otherwise the forum would just convert all the characters back and the function wouldn't work if you copied + pasted it.

You can download it here.

If you use that, and wrap it around $_POST[hello] you'll find it'll work fine. It basically just converts all types of symbol characters into ASCII which the browser can convert back. Such as:

$q = "UPDATE results SET hello = '" . convChars($_POST[hello]) . "' WHERE id = '1'";

 

[JamesM]

fantastic, just what I was looking for!

 

[Sic]

why would you do that? Can't you just use htmlentities($bar,ENT_QUOTES)? Also, use PDO. Also also, if you're not going to use PDO, escape your damn input!

 

[Teaf]

why would you do that? Can't you just use htmlentities($bar,ENT_QUOTES)? Also, use PDO. Also also, if you're not going to use PDO, escape your damn input!

I prefer the function instead, gives me more control over what is getting changed... but that's personal preference

 

[furnace]

$q = "UPDATE results SET hello = '" . mysql_real_escape_string($_POST[hello]) . "' WHERE id = '1'";

 

[Beansprout]

I always use a custom character conversion function which I wrap around every type of information that is going into a MySQL database which is plain text. You can see this function below, but I had to show it in a picture otherwise the forum would just convert all the characters back and the function wouldn't work if you copied + pasted it.

You can download it here.

If you use that, and wrap it around $_POST[hello] you'll find it'll work fine. It basically just converts all types of symbol characters into ASCII which the browser can convert back. Such as:

$q = "UPDATE results SET hello = '" . convChars($_POST[hello]) . "' WHERE id = '1'";

Your string almost certainly wouldn't still be safe to just throw into a query.

www.php.net/mysql_real_escape_string

Also instead of the replaces, you really should use www.php.net/htmlentities. You ain't getting more control there because you're doing the same to everything that comes in, so you may as well use htmlentities() and add anything else on. I'd imagine more control as meaning a case statement to sanitize based on input type, etc. If you want more control, use htmlspecialchars() to replace the special chars in HTML and then replace any others as necessary.

But now we're mixing up preventing HTML-based attacks with making a string safe for a database, which we don't want to do.

To make a string safe for data entry, use Sic's method or mysql_real_escape_string().

I prefer the function instead, gives me more control over what is getting changed

Careful, this is a path to a great wtf-ism I saw on dailywtf - the 'developer' defined TRUE and FALSE as constants to be 1 and 0 respectively. You know, in case they ever changed. Another one defined SPACE and EQUALS

 

[Moredhel]

Another one recommending PDO here.

<?php
    $dsn = 'mysql:host=localhost;dbname=foo';
    $db = new PDO($dsn, 'dbuser', 'dbpass');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $hello = filter_input(INPUT_POST, 'hello', FILTER_SANITIZE_SPECIAL_CHARS);
    if ($hello) {
        try {
            $statement = $db->prepare('UPDATE results SET hello = :hello WHERE id = :id');
            // do other stuff, could do the execute in a loop for instance, that's the beauty of prepared statements
            $statement->execute(array('hello' => $hello, 'id' => 1));
        } catch (Exception $e) {
            $log->error($e->getMessage());
            echo "friendly error message or something"
        }
    }
?>

 

[Sic]

you don't pass a persistent connection switch to the PDO constructor. I always do this to avoid exceeding maximum queries.