Could somebody please explain Prepared statements?

Question

I am currently using mysql_real_escape_string() but have heard of people using "Prepared Statements". So I would like to know if this way mysql_real_escape_string() is still safe, but if it isn't, could somebody explain how I could change my code to allow Prepared Statements?

$conn=mysql_connect("localhost", "u611142741_list", "REDACTED"); 
 mysql_select_db("u611142741_sugge", $conn);

// If the form has been submitted
if (trim($_POST["suggestion543"]) == "") {
echo "Error";
echo '<script language="javascript">';
echo 'alert("Invalid entry! Try Again.")';
echo '</script>';
echo '<script>';
echo 'setTimeout(function(){ window.location="" }, 500)';
echo '</script>';
} else {
$suggestion = mysql_real_escape_string($_POST['suggestion543']);
$ip = $_SERVER['REMOTE_ADDR'];
echo "Thank you for submitting your suggestion!";
echo '<script>';
echo 'setTimeout(function(){ window.location="" }, 2000)';
echo '</script>';
};



    // Build an sql statment to add the student details
    $sql="INSERT INTO suggestions

(`Suggestion`, `IP Address`) VALUES

('$suggestion','$ip')";
    $result = mysql_query($sql,$conn);


// close connection 
mysql_close($conn);

There are sixteen thousand million tutorials available on the Internet and many of them are on StackOverflow. Please do some research before asking on SO. — Stephan Vierkant, Nov 20 '14 at 20:45
`mysql_real_escape_string()` is not insecure in and of itself (though the API is deprecated and shouldn't be used any longer anyway), but is insecure inasmuch as _you_ are responsible for not forgetting to use it. And if you do forget even once, you have left open a vulnerability. Using prepared statements habitually and never passing variables directly to SQL strings eliminates the problem. — Michael Berkowski, Nov 20 '14 at 20:47
mysql_* functions are deprecated and shouldn't be used in the first place. Prepared statements are a well-documented feature, so that can't be a problem either. — Stephan Vierkant, Nov 20 '14 at 20:48
Basic examples are in [this reference question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) and this [good PDO tutorial for MySQL developers](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers) frames the use of the PDO extension in context of the `mysql_*()` extension you're already familiar with. But the most important part is `prepare()/bindParam()/execute()` which has _no equivalent functionality_ via `mysql_query()`. You must switch to a different API (PDO or MySQLi) to be able to use prepared statements.... — Michael Berkowski, Nov 20 '14 at 20:48
...but you must eventually do that _anyway_ because the `mysql_*()` functions will be removed from PHP in the future. Now is the time to learn PDO! — Michael Berkowski, Nov 20 '14 at 20:51
@Michael Berkowski Hmm.. Unfortunatly this may take some time as I cannot wrap my head around this new "PDO" — user4191887, Nov 20 '14 at 20:54
Here are some links that may help you. It has help me understand it http://prash.me/php-pdo-and-prepared-statements/ and https://www.youtube.com/watch?v=cbiLi0CBHNM at first I was having hard time couldn't get my head around it also just keep at it should take you a week or 2 to start understanding it maybe even sooner. — Donny, Nov 21 '14 at 00:37
possible duplicate of [Why shouldn't I use mysql\_\* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Cfreak, Nov 21 '14 at 05:09

score 2 · Accepted Answer · answered Nov 20 '14 at 20:49

Read up on them here http://php.net/manual/en/pdo.prepared-statements.php. Prepared statements don't escape characters, but they separate them from the SQL query they're attached to. You'll still want to escape any user-submitted data when you're outputting. When making SQL queries, however, prepared statements are a much more effective way of avoiding SQL injections. Escaping always leaves room for error, no matter how careful you are.

Could somebody please explain Prepared statements?

1 Answers1