Is this a secure method to insert form data into a MySQL database?

Question

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

so, what's exactly your problem? – Bogdacutu Mar 08 '12 at 20:20 — Bogdacutu, Mar 08 '12 at 20:20
How can I make this method secure? – Miles Pfefferle Mar 08 '12 at 20:24 — Miles Pfefferle, Mar 08 '12 at 20:24

score 21 · Accepted Answer · edited Jan 02 '19 at 16:58

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

score 8 · Answer 2 · edited Jan 02 '19 at 17:00

8

NO.

That is HIGHLY vulnerable to sql injection attacks.

Instead of using mysql_real_escape_string, I suggest using prepared statements.

edited Jan 02 '19 at 17:00

Neuron

5,141
5
38
59

answered Mar 08 '12 at 20:21

Daniel A. White

187,200
47
362
445

Okay, thanks. Any tips on how to modify it so that it is secure? – Miles Pfefferle Mar 08 '12 at 20:24
2

you should use prepared statements. – Daniel A. White Mar 08 '12 at 20:25

score 1 · Answer 3 · answered Mar 08 '12 at 20:21

1

Use mysql_real_escape_string.

answered Mar 08 '12 at 20:21

yunzen

32,854
11
73
106

score -1 · Answer 4 · edited Jan 02 '19 at 16:58

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

edited Jan 02 '19 at 16:58

Neuron

5,141
5
38
59

answered Mar 08 '12 at 20:25

yehuda

1,254
2
11
21

why the downvote? This is implemented by the author of Nova Fabrica! – yehuda Mar 08 '12 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 Mar 08 '12 at 20:30
2

dunno who is that author of Nova Fabrica but he apparently has no clue – Your Common Sense Mar 08 '12 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 Mar 08 '12 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 Mar 09 '12 at 05:26

Is this a secure method to insert form data into a MySQL database?

HTML form:

File insert.php:

4 Answers4

NO.

Linked