Hold on a minute! I do not mean the way to learn how to write unsecure code!
I just want to ask, how can I be sure is that my code is secure? If I learn from PHP books, such as sitepoint books, then do they may learn me something that can lead to security vulnerability? 
You don’t have to trust me on it because I’ll explain to you exactly why it is not just better buthow it actually makes data injection into the SQL impossible rather than just difficult as is the case when you use mysql_real_escape_string.
Here are two pieces of sample code - one using real_escape_string and one using a prepare statement.
mysql_real_escape_string:
$query = sprintf(
"SELECT address, age FROM friends WHERE firstname='%s' AND lastname='%s'",
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
$result = mysql_query($query);
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\
";
die($message);
}
while ($row = mysql_fetch_assoc($result)) {
$address = $row['address'];
$age = $row['age'];
}
Prepare:
if ($stmt = $mysqli->prepare(
"SELECT address, age FROM friends WHERE firstname=? AND lastname=?")) {
$stmt->bind_param("ss", $firstname, $lastname);
$stmt->execute();
$stmt->bind_result($address, $age);
$stmt->fetch();
$stmt->close();
} else {
$message = 'Invalid query: ' . $mysqli->error() . "\
";
die($message);
}
With the first of these the mysql_real_escape_string on the last_name is there to cater for where the last name contains an apostrophe (eg. O’Brien) as otherwise the apostrohe would be treated as part of the SQL rather than part of the data. Without the mysql_real_escape_string your '; DELETE FROM customers WHERE 1 or username = ’ in either field results in changing the actual SQL so as to run the delete.
In the second example using the prepare statement the SQL is in the prepare statement and the data is in the bind statement following. We don’t need to escape any apostrophes because they are a part of the data and cannot be confused for SQL because the SQL is in a completely different statement. Therefor the apostrophe in O’Brien doesn’t need escaping for it to get processed correctly and if '; DELETE FROM customers WHERE 1 or username = ’ is specified for last_name then the SQL looks for that string in the last_name field in the database, it doesn’t run the delete command because it is data and not SQL.
Data and SQL can only be confused for one another when the data isn’t escaped properly when both are included in the same string. Using prepare and bind keeps them in completely separate statements and so ensures that whatever is entered as data can never be accidentally treated as SQL.
I have heard from many sources that using prepared statements is “better”, and I don’t doubt that technically it probably is, but I still haven’t seen an example anywhere of how malicious code (through sql injection) can be executed in a query where mysql_real_escape_string() is used correctly.
Can you provide an example please.
Thanks, that helps. So granted you bind your variables correctly, you don’t have to catch the just in case stuff.
when I first read the subject line I thought the poster was going for the apple/microsoft business model in his php application.
validate all inputs to a server side script in the server side script itself before processing it in any way. You cannot rely on client side validation (using javascript) because users can turn javascript off and even if they have it turned on, javascript validation is very easy to bypass.
clean all inputs to database queries using mysql_real_escape_string()
That must be just you then considering shorter code to be more cumbersome than longer code. Just look back to my simple examples involving two fields back in post 7 - the prepare version is slightly shorter (but then there are only two data fields so you wouldn’t expect much difference).
With the code written properly the prepare and bind ends up much shorter than using an ordinary query for anything but the simplest SQL. For example if you have ten data fields in the query then the prepare/bind has immediately done away with mysql_real_escape_string() ten times - that’s 260 characters saved right there and your code is then much shorter than it is if you jumble the SQL and data together (where at the very least you run the risk of someone making a minor code change in the future that would allow SQL injection).
I suspect that the reason you do it the way you do is more due to your not having taken the time to see how much shorter using prepare/bind can make your code. Once you end up with a situation where you have to work with prepare/bind for a while and actually get used to the new way of doing things you’ll be wondering why you didn’t swap over sooner.
I use that from time to time, so I know what you mean by the autocomplete.
I use Netbeans as my IDE and you don’t have to type it out completely at all.
As I type in sql or php function names I get a pop up list of all available options and then simply click the function I want.
I only need to type about mysql_real and the complete function name would pop up.
If I need to use it again I can just copy and paste it. So typing is not an issue for me.
I think Stephen was talking about time it takes for you to type out, versus load time.
I thought I answered that question earlier with
I accept that prepared statements don’t leave anything to chance but I find the code a little more cumbersome - but that is just me.
I agree that if you forget to mysql_real_escape() an input then your code could be exposed but I am confident the chances of me making that mistake are extremely slim, but obviously not zero.
So the bottom line as I see it, prepared statements are more secure but mysql_real_escape_string() is still a totally valid option if used correctly - well for me at least.
and
The very first thing I do with any input data when it is received by the php script is to validate it to make sure it doesn’t contain any invalid characters - eg…I do not allow single or double quotes ,amongst a few other characters, in usernames and passwords.
And then even after the input values have passed validation, I then still wrap them in mysql_real_escape_string() when passed to an sql query.
The more layers of security, balanced with performance, the less likely you are to be hacked.
Maybe what I normally do is overkill - but so be it if it is.
So why are you not using prepare and bind statements to keep the SQL and data completely separate so as to make SQL injection completely impossible and mysql_real_escape_string completely unnecessary (so that it doesn’t matter if you forget to use it for one field since it effectively does nothing when you use prepare and bind to keep the SQL and data separated rather than jumbling the SQL and data together in a query and relying on escaping some characters in order to tell which is SQL and which is data).
checking that the input values are of the correct type is not enough imho because an input value could be a string, as expected, but the string may be an sql injection attempt.
The very first thing I do with any input data when it is received by the php script is to validate it to make sure it doesn’t contain any invalid characters - eg…I do not allow single or double quotes ,amongst a few other characters, in usernames and passwords.
And then even after the input values have passed validation, I then still wrap them in mysql_real_escape_string() when passed to an sql query.
The more layers of security, balanced with performance, the less likely you are to be hacked.
That is somewhat the point? You grab a query, make sure the inputed values are the type specified, run query.
I thought the same thing as this (post 6). Stephen answered that exact question in #7…
We don’t need to escape any apostrophes because they are a part of the data and cannot be confused for SQL because the SQL is in a completely different statement. Therefor the apostrophe in O’Brien doesn’t need escaping for it to get processed correctly and if '; DELETE FROM customers WHERE 1 or username = ’ is specified for last_name then the SQL looks for that string in the last_name field in the database, it doesn’t run the delete command because it is data and not SQL.
So instead of running that delete query, it looks for the literal value of the bolded part in the last_name field of your db. Since most names don’t look like that you get 0 hits vs deleting the table
ok thanks 
I accept that prepared statements don’t leave anything to chance but I find the code a little more cumbersome - but that is just me.
I agree that if you forget to mysql_real_escape() an input then your code could be exposed but I am confident the chances of me making that mistake are extremely slim, but obviously not zero.
So the bottom line as I see it, prepared statements are more secure but mysql_real_escape_string() is still a totally valid option if used correctly - well for me at least.
Everything that is set by user Input, that you use, should be verified and/or sanitized, even $_SERVER[‘REQUEST_URI’].
this includes database query variables ( variables that are set by user Input ), $_POST, $_GET, $_COOKIE, $_REQUEST.
I use it all the time, it’s a pretty good IDE - well I think it is - and I’ve been using it for a few years now.
For “quick and KISS” coding like trying to help others in these forums I use an old free version of the HTML-Kit editor.
Maybe it is, I don’t know.
But I don’t see any measurable performance hit in what is an insignificant amount of extra chars as a percentage of the total chars my class file.
I already did in post 7 of this thread. It doesn’t matter that using mysql_real_escape_string protects against injection - there’s no way to prove that there isn’t a way to bypass it plus it is easy to miss applying it to one variable and so leave your code exposed. That’s why the alternative where it isn’t needed at all is better because there is nothing to forget to call.