Because you are using prepared statements which eliminate the need to use mysqli_real_escape_string. Which is a good thing.
At least i am pretty sure that is what you are doing. Post some of your database code and I can tell you for sure. It’s also possible that you have another call to mysqli_real_escape_string in your code.
How did you know I was using Prepared Statements?
(I’m so darn confused with this thread and trying to learn this entire subject I forgot myself what I am doing?!)
It is hard to post all of my code, but here is a larger chunk…
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
</head>
<body>
<div id="wrapper" class="clearfix">
<div id="inner">
<?php
// <!-- Include BODY HEADER -->
// Initialize Errors Array.
$errors = array();
// Connect to the database.
// *******************************
// HANDLE FORM. *
// *******************************
if ($_SERVER['REQUEST_METHOD']=='POST'){
// Form was Submitted (Post).
// Trim all form data.
$trimmed = array_map('trim', $_POST);
// ********************
// CHECK FORM DATA. *
// ********************
// Check Article Title.
if (empty($trimmed['articleTitle'])){
$errors['articleTitle'] = 'Please enter an Article Title.';
}else{
// Not empty.
if (preg_match('#^[a-z-]{2,100}$#', $trimmed['articleTitle'])){
// Valid characters.
// *********************
// Check Title Availability. *
// *********************
// Build query.
$q = 'SELECT article_title
FROM article
WHERE article_title=?';
// Prepare statement.
$stmt = mysqli_prepare($dbc, $q);
// Bind variable.
mysqli_stmt_bind_param($stmt, 's', $trimmed['articleTitle']);
// Execute query.
mysqli_stmt_execute($stmt);
// Transfer result set from prepared statement.
// (Required for all queries that return results.)
mysqli_stmt_store_result($stmt);
if (mysqli_stmt_num_rows($stmt)==0){
// Unique Article Title.
// Escape problematic characters.
$articleTitle = mysqli_real_escape_string($dbc, $trimmed['articleTitle']);
}else{
// Duplicate Article Title.
$errors['articleTitle'] = 'This Article Title is already taken. Please choose a unique one.';
}
}else{
// Invalid entry.
$errors['articleTitle'] = 'Article Title must be 2-100 characters (a-z -)';
}
}
// Check Article Body.
if (empty($trimmed['body'])){
$errors['body'] = 'Please enter an Article Body.';
}else{
// Escape problematic characters.
$body = $trimmed['body'];
// $body = mysqli_real_escape_string($dbc, $trimmed['body']);
}
// Check for Data-Entry Errors.
if (empty($errors)){
// Form data clean.
// Add a new Article.
// Build query.
$q = "INSERT INTO article(article_title, html_title, meta_description, meta_keywords,
page_title, page_subtitle, written_on, author,
body, reference_listing, endnote_listing, created_on)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
// Prepare statement.
$stmt = mysqli_prepare($dbc, $q);
// Bind variable.
mysqli_stmt_bind_param($stmt, 'sssssssssss',
$articleTitle, $htmlTitle, $metaDescription, $metaKeywords,
$pageTitle, $pageSubtitle, $writtenOn, $author,
$body, $referenceListing, $endnoteListing);
// Execute query.
mysqli_stmt_execute($stmt);
// Verify Insert.
if (mysqli_stmt_affected_rows($stmt)==1){
// Insert Succeeded.
echo '<div id="box_Content_700b">';
echo '<h1>Article Added</h1>';
echo '<p>Congratulations!</p>
<p>The article: "' . stripslashes($pageTitle) . '" has been added to the database.</p>';
echo '</div>';
}else{
// Insert Failed.
echo '<div id="box_Content_700b">';
echo '<h1>Add Article Error</h1>';
echo '<p>The article could not be added due to a system error.</p>';
echo '</div>';
}// End of VERIFY INSERT.
// Close prepared statement.
mysqli_stmt_close($stmt);
// Close the connection.
mysqli_close($dbc);
Hope you can follow all of that code, because it is hard for me to follow at this point due to its length?!
Debbie
This is a tip off that you are using prepared statements:
$stmt = mysqli_prepare($dbc, $q);
And as I said before, this is a good thing. Use prepared statements and you can forget about ever needing to escape stuff heading for your database.
Now it looks like you still have some mysqli_real_escape_string calls lurking about. Make them all go away. As long as you are using mysqli_prepare (and stay away from mysql_query) then everything will be fine.
And once you have it all sorted out then switch to using PDO objects which will make your life even easier.
P.S. Use PHP tags instead of CODE tags and you get pretty colors.
Well, I have almost exclusively been using Prepared Statements because they are supposed to be more secure.
However, I have people tell me - and I think I also read online - that Prepared Statements are not an excuse for not escaping your data, because while Prepared Statements separate Data from the SQL, you can have nested quotes in your Data and thus that defeats the separation…
(Similar to my confusion about htmlentites, I have read and heard so many confusing and wrong things, I don’t know what the truth is any more.) 
So I added mysqli_escape_real_string right before I assigned my sanitized data to a variable like $body so that I didn’t have any lingering single quotes that could blow things up?!
(It sounds like you are telling me that since I am using a Prepared Statement for my INSERT, that I don’t need to use mysqli_escape_real_string prior to my INSERT, right??)
Now it looks like you still have some mysqli_real_escape_string calls lurking about. Make them all go away. As long as you are using mysqli_prepare (and stay away from mysql_query) then everything will be fine.
What is mysqli_query and why should I stay away from it?
And once you have it all sorted out then switch to using PDO objects which will make your life even easier.
Yeah, I’m not ready for OOP yet, but maybe someday?!
P.S. Use PHP tags instead of CODE tags and you get pretty colors.
Done!
Debbie
Yep!
What is mysqli_query and why should I stay away from it?
If you don’t know then don’t worry about. Forget I mentioned it.
However, I have people tell me - and I think I also read online - that Prepared Statements are not an excuse for not escaping your data, because while Prepared Statements separate Data from the SQL, you can have nested quotes in your Data and thus that defeats the separation…
If you can post a link then I’ll be glad to look at it. But it simply makes no sense whatsoever to escape data when using prepared statements.
So to keep my data safe when entering into my database I can safely get by using Prepared Statements (and Regular Expressions), right?
And to keep my website safe when outputting data to the screen whether from my database or just calculated code, I should use htmlentities or htmlspecialchars, right?
I’m sure it is more complex than that, but I just want something reasonably safe for now.
Debbie
None of this has anything to do with security - if you want security then read up on sanitizing and validating your data.
Escaping it to stop data being confused with commands.
For example <em>x < y</em> needs the "x < y’ part escaped because otherwise the < would be misinterpreted as a part of a tag instead of as part of the text. The rest should not be escaped as those are supposed to be tags.
With prepare statements for the database there is nothing needing to be escaped because the data and the sql are kept completely separate - for security you still need to validate the data before you store it though (that’s where you might use a regular expression if there isn’t a built in function eg is_numeric() or validation filter - see PHP: Validate filters - Manual - that will do it for you without needing to try to create an appropriate regular expression).
Hello…
If someone uses a single quote to trip up SQL and they are able to insert extra SQL commands - read “SQL Injection” - that most certainly has to do with security!!!
That is why Prepared Statements were created!
For example <em>x < y</em> needs the "x < y’ part escaped because otherwise the < would be misinterpreted as a part of a tag instead of as part of the text. The rest should not be escaped as those are supposed to be tags.
You are describing an instance where htmlentities would be used, and I would call that “escaping” which is more like \’ but I suppose you could use that term?!
With prepare statements for the database there is nothing needing to be escaped because the data and the sql are kept completely separate
And what about…
Debbie’s Mother’s Aunt’s name is also Debbie!
You are telling me that internally PHP doesn’t “escape” those apostrophes/single quotes in the bound parameters?!
for security you still need to validate the data before you store it though (that’s where you might use a regular expression if there isn’t a built in function eg is_numeric() or validation filter - see PHP: Validate filters - Manual - that will do it for you without needing to try to create an appropriate regular expression).
But Regular Expressions can be used for security as well.
For instance, this code…
WHERE email = 'x'; DROP TABLE members; --';
…could be prevented if you used Regular Expressions.
Debbie
No - that is why you validate fields since SQL is not a valid value for a name or an address or any other field that you are likely to use. The code for injection should never get anywhere near the SQL statements for it to matter how the SQL is coded. Prepare statements are to allow the SQL and data to be kept separate - that it acts as a form of security for those who stupidly forgot to apply the security on the input where it belongs is like the airbags in your car that reduce the chance of getting killed because with an airbag who needs brakes. Prepare statements are like the airbag, security through validation is the brakes that are supposed to stop you before you go head on into a truck going the opposite direction (the sql injection).
Yes, you have to escape the content when outputting to an HTML document because unlike SQL where you can keep the data and commands separate, there is no equivalent for HTML so you must escape < in the data as < - the best function to do that us htmlspecialchars() as it doesn’t convert lots of characters that don’t need to be escaped the way htmlentities() does.
Since the mysqli_real_escape_string rins SQL commands and not PHP the PHP does NOTHING while the SQL processor is handling it.
Yes regular expressions can be used for security but usually with PHP there’s a validation built in that can do it so that you are not relying on getting the regular expression right. This is particularly true if you are not a regular expression expert who can be certain that their expression allows everything that’s valid and blocks everything that’s not. I have only been using regular expressions for ten years or so and so I am certainly not expert enough with regular expressions to know that I can write ones that will work as accurately as the PHP filters that were written by experts will work and so I will rely on their expertise rather than my own with that - of course if you invented regular expressions then you might be in a position where you know that yours will be at least as accurate as those in the filters.
Any security should be applied as you read the $_POST and $_GET fields into the PHP right at the start - you then know that security is taken care of and can then consider connecting to the database or whatever else it is you want to do in the script with that secure data.
FIEO.
The first eight articles listed should clear all of this up.
DoubleDee,
Part of the problem might be the term “security”. When I think of security I tend to think of hackers coming in and stealing or corrupting my data. But data security really encompasses just about anything that might cause data loss or corruption. For example, backing up your database is part of security.
So when a user enters some data into one of your forms with the expectation that the data be properly stored and later retrieved then we are, in a somewhat abstract sense, talking security. When using straight sql (as opposed to prepared statements) you need to escape your data only because it won’t store properly if you don’t. Nothing really to do with hacking or what not.
It might be useful to examine the documentation.
PHP: mysqli::real_escape_string - Manual
This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to an escaped SQL string, taking into account the current character set of the connection.
It’s all focused on making sql strings which contain data. With prepared statements, your sql statements don’t contain data and thus the whole thing about escaping data intended to be stored in a database goes away.
No - that is why you validate fields since SQL is not a valid value for a name or an address or any other field that you are likely to use. The code for injection should never get anywhere near the SQL statements for it to matter how the SQL is coded.
Absolutely correct!!!