49

Possible Duplicate:
How can I prevent SQL injection in PHP?

This is the example on w3schools.org:

HTML form:

<html>
    <body>
        <form action="insert.php" method="post">
            Firstname: <input type="text" name="firstname" />
            Lastname: <input type="text" name="lastname" />
            Age: <input type="text" name="age" />
            <input type="submit" />
        </form>
    </body>
</html>

File insert.php:

<?php
    $con = mysql_connect("localhost","peter","abc123");
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("my_db", $con);

    $sql="INSERT INTO Persons (FirstName, LastName, Age)
          VALUES
          ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

    if (!mysql_query($sql,$con))
    {
        die('Error: ' . mysql_error());
    }
    echo "1 record added";

    mysql_close($con)
?>

I've read through other questions on here, but I couldn't find a direct answer, as most were much more complicated.

I looked at How can I prevent SQL injection in PHP?, but I'm a bit confused on how to modify this:

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute(array(':column' => $unsafeValue));

Assuming I used the HTML form above and wanted to insert the data from field 'firstname' into the database, should it look like this? Or am I supposed to modify column?:

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute(array(':column' => $firstname));
Improve this question
2
  • 1
    so, what's exactly your problem?
    – Bogdacutu
    Commented Mar 8, 2012 at 20:20
  • How can I make this method secure?
    – Ten Sleep
    Commented Mar 8, 2012 at 20:24

4 Answers 4

Reset to default
21

The example you provided inserts the post vars into the database without first analyzing them for evil user input. Use type casting, escaping/filter functions, prepared statements etc. before using them to interact with your DB.

A general rule to go by is to never trust user input. EVER!

Check out: Best way to stop SQL Injection in PHP

In response to your question, here is how you'd handle the entire form using PDO prepared statements.

$stmt = $db->prepare('INSERT INTO Persons (FirstName, LastName, Age) VALUES (:first_name, :last_name, :age)');

$stmt->execute(array(':first_name' => $first_name,':last_name' => $last_name, ':age' => $age));

If you just want to insert one column in the record like you asked, the syntax would be:

$stmt = $db->prepare('INSERT INTO Persons (FirstName) VALUES (:first_name)');

$stmt->execute(':first_name', $first_name);
Improve this answer
0
8

NO.

That is HIGHLY vulnerable to sql injection attacks.

Instead of using mysql_real_escape_string, I suggest using prepared statements.

Improve this answer
2
  • Okay, thanks. Any tips on how to modify it so that it is secure?
    – Ten Sleep
    Commented Mar 8, 2012 at 20:24
  • 2
    you should use prepared statements.
    – Daniel A. White
    Commented Mar 8, 2012 at 20:25
1

Use mysql_real_escape_string.

Improve this answer
-1 
$magic_quotes_active = get_magic_quotes_gpc();
$real_escape_string_exists = function_exists('mysql_real_escape_string');

function escape_value($sql) {
    if ($real_escape_string_exists) {
        if($magic_quotes_active) {
            $sql = stripslashes($sql);
        }
        $sql = mysql_real_escape_string($sql);
    } else {
        if(!$magic_quotes_active) {
            $sql = addslashes($sql);
        }
    }
    return $sql;
}

This is considered a very secure way to insert stuff into a database. Use the returned $sql to as your query!

Improve this answer
5
  • why the downvote? This is implemented by the author of Nova Fabrica! <a href="novafabrica.com">
    – yehuda
    Commented Mar 8, 2012 at 20:27
  • I didn't downvote - but just to be clear, this function can only be used to escape a single value, not the entire SQL string.
    – AndrewR
    Commented Mar 8, 2012 at 20:30
  • 2
    dunno who is that author of Nova Fabrica but he apparently has no clue
    – Your Common Sense
    Commented Mar 8, 2012 at 20:33
  • @shrapnel, big talk, he happpens to be Kevin Skoglund and is an excellent Php programmer. What is your problem with his function?
    – yehuda
    Commented Mar 8, 2012 at 20:37
  • 1
    First, I removed this irrelevant link. Next, the main problem with this function is mixing magic quotes stuff with escaping strings. Magic quotes is a site-wide problem, irrelevant to SQL. It spoils any data, not only SQL strings (cookies, for example). Thus, magic quotes have to be either turned off or stripped out at the bootstrap, despite of any SQL activity. While mysql_real_escape_string has very limited use - it have to be used to escape SQL strings only. See - this is completely different matters, one cannot mix them.
    – Your Common Sense
    Commented Mar 9, 2012 at 5:26

Not the answer you're looking for? Browse other questions tagged or ask your own question.