how to escape characters from external source to insert into database?

Question

insert.php is behind htaccess/passwd

It is grabbing data from an external source and then converting this into variables for insertion to database.

I am getting a mysql error that I believe is being caused by the existence of left and right parentheses ie (some text here) in the external source.

I've used mysql_real_escape_string but it doesn't seem to be working in this case.

$con = mysql_connect("localhost","user_name","password");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("user_dbname", $con);

// escape characters
$escaped_value = mysql_real_escape_string($var);

$sql = "INSERT INTO data (field1, field2, field3, field4, field5, field6)
        VALUES ('$_POST[field1]','$_POST[field2]','$_POST[field3]','$_POST[field4]',
                '$field5','$escaped_value', )";
;

Maybe it's because you only use it in one out of six columns... :-? — Álvaro González, Nov 06 '12 at 13:17
possible duplicate of [Best way to prevent SQL injection?](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection) — mario, Nov 06 '12 at 13:21
I'd like to visit your site, BTW, my name is `Robert'); DROP TABLE data; --` — Elias Van Ootegem, Nov 06 '12 at 13:27

score 0 · Answer 1 · answered Nov 06 '12 at 13:17

0

Use mysql_real_escape_string method and you can put the variables between {} characters

answered Nov 06 '12 at 13:17

avolquez

753
1
7
20

GarethL · Accepted Answer · 2012-11-06T13:28:07.107

You've got a comma after the last data field which is causing the query to fail. Also, you should refer to the $_POST array variables as $_POST['field1'] and not $_POST[field1]

Try

$sql = "INSERT INTO data (field1, field2, field3, field4, field5, field6)
    VALUES ('".$_POST['field1']."','".$_POST['field2']."','".$_POST['field3']."','".$_POST['field4']."',
            '$field5','$escaped_value')";

Effectively, mysql_real_escape_string is the least of your problems. :)

score 0 · Answer 3 · answered Nov 06 '12 at 13:25

The best approach is to use prepared statments, you're going to have to, eventually anyway: see the red box? the mysql_* extension is being deprecated. Use the alternatives: PDO or mysqli_*

try
{
    $db = new PDO();//connect, pass correct data here
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//cause PDO to throw PDOExceptions on error
    $stmt = $db->prepare('INSERT INTO data (field1, field2, field3, field4, field5, field6)
            VALUES (:field1, :field2,:field3,:field4, :field5,:field6)');
    //make array with values, no need to use escape-functions on $var:
    $bind = array(':field1' => $_POST['field1'],':field2' => $_POST['field2'],
                  ':field3' => $_POST['field3'],':field4' => $_POST['field4'],
                  ':field5' => $field5,':field6' => $var);
    if ($stmt->execute($bind))//pass array here
    {
        return true;//data inserted
    }
}
catch(PDOException $e)
{
    echo $e->getMessage();
    $db = null;
    return false;
}

how to escape characters from external source to insert into database?

3 Answers3