Alternative to MySQL_Real_Escape_String Without Connecting to Db

Alternative to mysql_real_escape_string without connecting to DB

It is impossible to safely escape a string without a DB connection. mysql_real_escape_string() and prepared statements need a connection to the database so that they can escape the string using the appropriate character set - otherwise SQL injection attacks are still possible using multi-byte characters.

If you are only testing, then you may as well use mysql_escape_string(), it's not 100% guaranteed against SQL injection attacks, but it's impossible to build anything safer without a DB connection.

Alternative to mysql_real_escape_string for PHP

That's not what mysql_real_escape_string does or did (the functions are now deprecated). An alternative to mysql_real_escape_string is using prepared statements, for example with PDO or MySQLi.

However, that's completely unrelated to stripping Javascript or PHP code from a string - also; it could be relatively hard to identify 'Javascript' or 'PHP'.

The real question here is; why do you wanna strip it? The danger doesn't reside in saving the data, the danger resides in displaying the data. You should never ever execute code entered by the user, be it Javascript or PHP.

As for Javascript, disallowing HTML tags in your output is enough. Look into functions as strip_tags, or even better, htmlspecialchars. Preventing PHP from execution is even easier; just do not use the method eval.

alternative to mysql_real_escape_string (access_denied)

you can use this function if you mysteriously want to escape values without a database connection :

<?php
function mysql_escape_mimic($inp) {
    if(is_array($inp))
        return array_map(__METHOD__, $inp);

    if(!empty($inp) && is_string($inp)) {
        return str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $inp);
    }

    return $inp;
}
?>

Safe alternative to mysql_real_escape_string? (PHP)

• It's terrible idea to connect every time you're calling this function. A good planned application wouldn't have such odd limitation.

• you can use substitutions, like this
  
  myquery("SELECT * FROM table WHERE id = %s","My string");

• You can use another way of substitutions, a modern one: prepared statements. it will be described in numerous other answers.

as noone posted it yet, here is rough example

function fetchAll(){
 $args = func_get_args();
 $query = array_shift($args);
 $stmt = $pdo->prepare($query);
 $stmt->execute($args);
 return $stmt->fetchAll();
}
$a=$db->fetchAll("SELECT * FROM users WHERE status=? LIMIT ?,?",$status,$start,$num);

• As long as you're using single-byte encoding or utf-8, no need to use mysql_real_escape_string, so mysql_escape_string(deprecated) or addslashes would be enough

PHP mysql_real_escape_string or alternative with PDO

mysql_real_escape_string requires a connection because its output depends on the connection character set.

If you are able to sync character sets manually (or if you never change it), you may write your own implementation.

Shortcomings of mysql_real_escape_string?

The main shortcoming of mysql_real_escape_string, or of the mysql_ extension in general, is that it is harder to apply correctly than other, more modern APIs, especially prepared statements. mysql_real_escape_string is supposed to be used in exactly one case: escaping text content that is used as a value in an SQL statement between quotes. E.g.:

$value = mysql_real_escape_string($value, $link);
$sql = "... `foo` = '$value' ...";
                     ^^^^^^

mysql_real_escape_string makes sure that the $value in the above context does not mess up the SQL syntax. It does not work as you may think here:

$sql = "... `foo` = $value ...";

or here:

$sql = "... `$value` ...";

or here:

$sql = mysql_real_escape_string("... `foo` = '$value' ...");

If applied to values which are used in any context other than a quoted string in an SQL statement, it is misapplied and may or may not mess up the resulting syntax and/or allow somebody to submit values which may enable SQL injection attacks. The use case of mysql_real_escape_string is very narrow, but is seldom correctly understood.

Another way to get yourself into hot water using mysql_real_escape_string is when you set the database connection encoding using the wrong method. You should do this:

mysql_set_charset('utf8', $link);

You can also do this though:

mysql_query("SET NAMES 'utf8'", $link);

The problem is that the latter bypasses the mysql_ API, which still thinks you're talking to the database using latin1 (or something else). When using mysql_real_escape_string now, it will assume the wrong character encoding and escape strings differently than the database will interpret them later. By running the SET NAMES query, you have created a rift between how the mysql_ client API is treating strings and how the database will interpret these strings. This can be used for injection attacks in certain multibyte string situations.

There are no fundamental injection vulnerabilities in mysql_real_escape_string that I am aware of if it is applied correctly. Again though, the main problem is that it is terrifyingly easy to apply it incorrectly, which opens up vulnerabilities.

mysql_escape_string VS mysql_real_escape_string

The difference is that mysql_escape_string just treats the string as raw bytes, and adds escaping where it believes it's appropriate.

mysql_real_escape_string, on the other hand, uses the information about the character set used for the MySQL connection. This means the string is escaped while treating multi-byte characters properly; i.e., it won't insert escaping characters in the middle of a character. This is why you need a connection for mysql_real_escape_string; it's necessary in order to know how the string should be treated.

However, instead of escaping, it's a better idea to use parameterized queries from the MySQLi library; there has previously been bugs in the escaping routine, and it's possible that some could appear again. Parameterizing the query is much, much harder to mess up, so it's less likely that you can get compromised by a MySQL bug.

manually specifying mysql_real_escape_string charset so no db connection required - possible?

I'd suggest a different approach:

function search(array $params) {
    $cacheKey = md5(serialize($params));

    ... search cache ...

}

You don't need to run the hash on the query, you can just run it on the supplied parameters. The query would supposedly be the same every time.

PHP mysqli_real_escape_string argument

Alternative to mysql_real_escape_string without connecting to DB

This basically explains why. The has to know what char set the MySQL connection uses. If you don't, multi-byte SQL injections may be possible, depending on your code. Anyways, you are required to use a MySQL instance unless you write your own function.

---

Related Topics