Are mysql_real_escape_string() and mysql_escape_string() Enough to Secure MySQL Applications?

MySQL Security: Are mysql_real_escape_string() and mysql_escape_string() Adequate?

The efficacy of mysql_real_escape_string() and mysql_escape_string() for application security has sparked some debate. While these functions prevent against known SQL injection vectors, their limitations might leave you vulnerable to more advanced attacks.

SQL Injection Susceptibility

Despite using mysql_real_escape_string(), you may still be susceptible to SQL injections in scenarios where PHP variables are integrated into queries. For instance, consider this code:

<code class="php">$sql = "SELECT number FROM PhoneNumbers " .
       "WHERE " . mysql_real_escape_string($field) . " = " . mysql_real_escape_string($value);</code>

A sophisticated hacker could exploit this query with the following input:

<code class="php">$field = "1=1"
$value = "1"</code>

This bypasses the intended logic, returning (potentially) all records instead of those matching the specified criteria.

LIKE Attacks

mysql_real_escape_string() is ineffective in preventing LIKE attacks, such as:

<code class="php">$sql = "SELECT number FROM PhoneNumbers " .
       "WHERE " . mysql_real_escape_string($field) . " LIKE " . mysql_real_escape_string($value);</code>

A malicious user could set $value to % to retrieve all records, potentially exposing sensitive data.

Charset Exploits

Internet Explorer remains vulnerable to charset exploits, even in 2011. This vulnerability can grant attackers control over your database, similar to SQL injections.

Prepared Statements: A Proactive Approach

Both mysql_real_escape_string() and mysql_escape_string() are vulnerable because they are reactive defense mechanisms. Prepared statements, on the other hand, provide a proactive solution. By executing only valid and programmed SQL, prepared statements dramatically reduce the risk of unexpected SQL execution, regardless of the vulnerabilities of the underlying database.

Here's an example using prepared statements:

<code class="php">$statement = $pdo->prepare('SELECT url FROM GrabbedURLs ' .
                           'WHERE ' . $column . '=? ' .
                           'LIMIT ' . intval($limit));
$statement->execute(array($value));</code>

Prepared statements are both secure and less verbose than employing mysql_real_escape_string(). They rely on the protective measures of the database server to safeguard against known and unknown threats.

The above is the detailed content of Are mysql_real_escape_string() and mysql_escape_string() Enough to Secure MySQL Applications?. For more information, please follow other related articles on the PHP Chinese website!