MySQL Error When Using Apostrophes in Text Box of Autoresponder

Question

I'm having a problem with apostrophes entered into the text box of the message area. I get an error message that the incorrect syntax is used and to check the mysql manual for the correct syntax. Below is the exact error I get.

---

MySQL Error: 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'm just checking to make sure you received your Free Download. If not, here is' at line 5)

---

I've checked with my hosting service, but they can't help me. I've called and email the software developer of the autoresponder, but they do not respond. (later found why, with all the complaints against them). I searched online and found some sites, like this one, but I have no idea how to setup the string or where to put it.

Please help. Thanks, Denise

Answer 1

Try using two apostrophes for every apostrophe. If this works then do a replace on validation before the sql is processed by the database.

Answer 2

If you are using PHP make your post or get variables safe and sql validate by using "mysql_real_escape_string". This will make your string safe from " SQL Injection too"

<?php

if (isset($_POST['product_name']) && isset($_POST['product_description']) && isset($_POST['user_id'])) {
    // Connect

    $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password');

    if(!is_resource($link)) {

        echo "Failed to connect to the server\n";
        // ... log the error properly

    } else {

        // Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.

        if(get_magic_quotes_gpc()) {
            $product_name        = stripslashes($_POST['product_name']);
            $product_description = stripslashes($_POST['product_description']);
        } else {
            $product_name        = $_POST['product_name'];
            $product_description = $_POST['product_description'];
        }

        // Make a safe query
        $query = sprintf("INSERT INTO products (`name`, `description`, `user_id`) VALUES ('%s', '%s', %d)",
                    mysql_real_escape_string($product_name, $link),
                    mysql_real_escape_string($product_description, $link),
                    $_POST['user_id']);

        mysql_query($query, $link);

        if (mysql_affected_rows($link) > 0) {
            echo "Product inserted\n";
        }
    }
} else {
    echo "Fill the form properly\n";
}
?>