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? Commented Mar 8, 2012 at 20:20
  • How can I make this method secure? 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
Sign up to request clarification or add additional context in comments.

Comments

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 Comments

Okay, thanks. Any tips on how to modify it so that it is secure?
you should use prepared statements.
1

Use mysql_real_escape_string.

Improve this answer

Comments

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

why the downvote? This is implemented by the author of Nova Fabrica! <a href="novafabrica.com">
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.
dunno who is that author of Nova Fabrica but he apparently has no clue
@shrapnel, big talk, he happpens to be Kevin Skoglund and is an excellent Php programmer. What is your problem with his function?
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.