What to use instead of mysql_real_escape_string?

I just read on php.net that mysql_real_escape_string is invalid after php 5.5 and to use MySQLi or PDO_MySQL. Hundreds of website tutorials currently tell you the best way to escape strings is 'mysql_real_escape_string' and I don't see a single alternative example given yet.

So, does anyone know how to use MySQLi or PDO_MySQL to give me the same outcome as this?
$variable = mysql_real_escape_string($variable);

or this?:


UPDATE `db` SET `comment` = '".mysql_real_escape_string($comment)."'

 

You use prepared statements for mysqli (I also believe for PDO, though I use mysqli), like the following:

function update_content($id, $page, $user_date) {
 
global $db;
 
// define sensible test values
 
// create a prepared statement
$update_stmt = $db->prepare('
UPDATE
pages
SET
blog_name =?,
content = ?,
update_date = ?
WHERE
id = ?
');
 
/* bind parameters for markers */
$update_stmt->bind_param('ssss', $page['blog_name'], $page['content'], $user_date, $id);
 
/* execute query */
$update_stmt->execute();
 
/* close statement */
$update_stmt->close();
}

PHP:

An you protect yourself from anything a user can change by doing the following:

function html_escape($html_escape) {
        $html_escape =  htmlspecialchars($html_escape, ENT_QUOTES | ENT_HTML5, 'UTF-8');
        return $html_escape;
    }

PHP:

$username = html_escape($_POST['username']);

PHP:

Well that is what I do at least....

and lastly

echo "<form action=\"edit_page.php?page=" . urlencode($page['id']) . "\" method=\"post\">"

PHP:

Found something interesting on php.net
$query_string = 'foo=' . urlencode($foo) . '&bar=' . urlencode($bar);
echo '<a href="mycgi?' . htmlentities($query_string) . '">';
I even have learned something.

 

strider64 hit it on the head -- you don't NEED functions like that if you use prepared queries, which auto-sanitize for you when you use bindparam, or optionally in PDO pass an array to ->execute.

You should not be building values into your query strings, AT ALL.

For example, your little query as PDO would be something like:

$statement=$db->prepare('
	UPDATE db
	SET comment = :comment
	WHERE id = :id
');
$statement->execute(array(
	':comment' => $comment,
	':id' => $id
));

Code (markup):

Hell, you could even do this:

$statement->execute(array(
	':comment' => $_GET['comment'],
	':id' => $_COOKIE['id']
));

Code (markup):

... and it's completely safe.

Strider64 is also correct in that you should urlencode output for URL's in the markup or htmlspecialchars (not sure what that extra function is for) your echo values on anything that's user input, but that's completely unrelated to anything to do with the database.

Though @Strider64, NOT that I'd allow the database connection to be a global, NOT that I'd have such pointlessly silly and redundant comments... etc, etc...

In general, sanitization of values through prepared queries is WHY you should be using either PDO or mySQLi (I prefer PDO) in the first place, and part of why they added those big red boxes to php.net telling us to stop using mysql_ functions.

Though your code makes me ask another question -- why do people keep putting single quotes in their queries for nothing?!?

 

@deathshadow - Ooops, I meant to take out that global $db, it was old code that I had laying around. As for the adding function html_escape it's just my way of remembering to encapsulate my output and to ensure it matches the proper Unicode character set (ETML-HTM5, ETML-HTM401, etc...). Though I found that htmlspecialchars has never failed using it on it's own, I'm just probably being paranoid.

 

Heh, paranoid is usually a good thing when it comes to PHP - it is basically insecure by design. See the lengths I go to keeping $db out of scope except to what needs it, destroying the SQL login info as soon as it's used and restricting what can access it to one function in one file using debug_backtrace, etc, etc...

So I can accept that answer with a grin.

My version of that paranoia is to only allow the 7 bit part of the ASCII character set in anything that's "code"... and only deploying user generated content as UTF-8. It doesn't fit that, it doesn't go on my sites.

 

Because the examples show it, and most code is just copied with no understanding?

Nah, everyone who writes code actually understands what it does, right?

 

That's very helpful.

What is the minimum PHP version I need for using mysqli or PDO?

 

Mysqli = php 5.0 minimum to use?
PDO = php 5.28 minimum to use?

This is the only thing I found via searches - couldn't find on php.net.

Is this accurate?

 

To my knowledge, both became available with PHP 5.0. I would not say there is really a minimum version for either. However at this point, regardless of PDO or MySQL, you should be using PHP 5.3 at least.

 

... and there's no LEGITIMATE reason not to be on the latest (5.4.13 as of writing this post) apart from piss poor server administration or goofy outdated insecure code you probably shouldn't be using on a website anyways.

Oh, and PDO was added in 5.1 -- which is to say, BACK IN 2005.

so yeah, eight years...