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

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
– Bogdacutu

2012-03-08 20:20:51 +00:00
Commented Mar 8, 2012 at 20:20 — Bogdacutu
– Bogdacutu, Commented Mar 8, 2012 at 20:20
How can I make this method secure?

Miles P
– Miles P

2012-03-08 20:24:05 +00:00
Commented Mar 8, 2012 at 20:24 — Miles P
– Miles P, Commented Mar 8, 2012 at 20:24

Georg Plaz · Accepted Answer · 2019-01-02 16:58:28Z

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

Georg Plaz · Accepted Answer · 2019-01-02 17:00:42Z

8

NO.

That is HIGHLY vulnerable to sql injection attacks.

Instead of using mysql_real_escape_string, I suggest using prepared statements.

edited Jan 2, 2019 at 17:00

Georg Plaz

6,0075 gold badges43 silver badges66 bronze badges

answered Mar 8, 2012 at 20:21

Daniel A. White

192k49 gold badges389 silver badges472 bronze badges

2 Comments

Miles P Over a year ago

Okay, thanks. Any tips on how to modify it so that it is secure?

Daniel A. White Over a year ago

you should use prepared statements.

yunzen · Accepted Answer · 2012-03-08 20:21:42Z

1

Use mysql_real_escape_string.

answered Mar 8, 2012 at 20:21

yunzen

33.5k13 gold badges79 silver badges113 bronze badges

Comments

Georg Plaz · Accepted Answer · 2019-01-02 16:58:38Z

-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 2, 2019 at 16:58

Georg Plaz

6,0075 gold badges43 silver badges66 bronze badges

answered Mar 8, 2012 at 20:25

yehuda

1,2822 gold badges11 silver badges21 bronze badges

5 Comments

yehuda Over a year ago

why the downvote? This is implemented by the author of Nova Fabrica! <a href="novafabrica.com">

AndrewR Over a year ago

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.

Your Common Sense Over a year ago

dunno who is that author of Nova Fabrica but he apparently has no clue

yehuda Over a year ago

@shrapnel, big talk, he happpens to be Kevin Skoglund and is an excellent Php programmer. What is your problem with his function?

Your Common Sense Over a year ago

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.

Collectives™ on Stack Overflow

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

HTML form:

File insert.php:

4 Answers 4

Comments

NO.

2 Comments

Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

HTML form:

File insert.php:

4 Answers 4

Comments

NO.

2 Comments

Comments

5 Comments

Linked

Related