SQL injection that gets around mysql_real_escape_string()
Consider the following query:
$iId = mysql_real_escape_string("1 OR 1=1");
$sSql = "SELECT * FROM table WHERE id = $iId";
mysql_real_escape_string()
will not protect you against this.
The fact that you use single quotes (' '
) around your variables inside your query is what protects you against this. The following is also an option:
$iId = (int)"1 OR 1=1";
$sSql = "SELECT * FROM table WHERE id = $iId";
Is mysql_real_escape_string enough to Anti SQL Injection?
mysql_real_escape_string
is usually enough to avoid SQL injection. This does depend on it being bug free though, i.e. there's some small unknown chance it is vulnerable (but this hasn't manifested in the real world yet). A better alternative which completely rules out SQL injections on a conceptual level is prepared statements. Both methods entirely depend on your applying them correctly; i.e. neither will protect you if you simply mess it up anyway.
Can mysql_real_escape_string ALONE prevent all kinds of sql injection ?
mysql_real_escape_string ALONE can prevent nothing.
Moreover, this function has nothing to do with injections at all.
Whenever you need escaping, you need it despite of "security", but just because it is required by SQL syntax. And where you don't need it, escaping won't help you even a bit.
The usage of this function is simple: when you have to use a quoted string in the query, you have to escape it's contents. Not because of some imaginary "malicious users", but merely to escape these quotes that were used to delimit a string. This is extremely simple rule, yet extremely mistaken by PHP folks.
This is just syntax related function, not security related.
Depending on this function in security matters, believing that it will "secure your database against malicious users" WILL lead you to injection.
A conclusion that you can make yourself:
No, this function is not enough.
Prepared statements is not a silver bullet too. It covers your back for only half of possible cases. See the important addition I made to the famous question for the details
Is it possible to hack mysql_real_escape_string() Htmlspecialchars()?
If your PHP is updated try to use mysqli or PDO and prepared statements
But to answer your question, YES mysql_real_escape_string() can be injected, but it's very complicated to do so. Here's a example
Does mysql_real_escape_string() FULLY protect against SQL injection?
According to Stefan Esser, "mysql_real_escape_string()
[is] not safe when SET NAMES
is used."
His explanation, from his blog:
SET NAMES is usually used to switch the encoding from what is default to what the application needs.
This is done in a way thatmysql_real_escape_string
doesn’t know about this. This means if you switch to some multi byte encoding that allows backslash as 2nd 3rd 4th… byte you run into trouble, becausemysql_real_escape_string
doesn’t escape correctly. UTF-8 is safe…Safe way to change encoding is
mysql_set_charset
, but that is only available in new PHP versions
He does mention that UTF-8 is safe, though.
Is mysqli_real_escape_string enough to avoid SQL injection or other SQL attacks?
Could someone tell me if it is secure or if it is vulnerable to the SQL Injection attack or other SQL attacks ?
No. As uri2x says, see SQL injection that gets around mysql_real_escape_string()
.
The best way to prevent SQL injection is to use prepared statements. They separate the data (your parameters) from the instructions (the SQL query string) and doesn't leave any room for the data to contaminate the structure of your query. Prepared statements solve one of the fundamental problems of application security.
For situation where you cannot use prepared statements (e.g. LIMIT
), using a very strict whitelist for each specific purpose is the only way to guarantee security.
// This is a string literal whitelist
switch ($sortby) {
case 'column_b':
case 'col_c':
// If it literally matches here, it's safe to use
break;
default:
$sortby = 'rowid';
}
// Only numeric characters will pass through this part of the code thanks to type casting
$start = (int) $start;
$howmany = (int) $howmany;
if ($start < 0) {
$start = 0;
}
if ($howmany < 1) {
$howmany = 1;
}
// The actual query execution
$stmt = $db->prepare(
"SELECT * FROM table WHERE col = ? ORDER BY {$sortby} ASC LIMIT {$start}, {$howmany}"
);
$stmt->execute(['value']);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
I posit that the above code is immune to SQL injection, even in obscure edge cases. If you're using MySQL, make sure you turn emulated prepares off.
$db->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
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.
Sql injection mysql_real_escape_string
You should be able to use str_replace to help with this:
mysql_real_escape_string(str_replace(array("\n", "\r\n", "\x00", '"', '\''), '', $input));
Having said that, it is a good idea to switch to mysqli or PDO for database read / write. Both of these allow prepared statements, which reduce the risk of SQL injections.
Here's an example of PDO:
$stmt = $PDOConnection->prepare('INSERT INTO example_table (input_field) VALUES (:input_field)');
$stmt->bindParam(':input_field', $input);
$stmt->execute();
Related Topics
How to Use a Findby Method With Comparative Criteria
Session Data Not Preserved After Redirection
How to Compare Comma Separated Ids on Left Join
How to Install Extension for PHP Via Docker-Php-Ext-Install
Get Total Number of Members in Discord Using PHP
Laravel Ajax Response Return HTML Elements
Undefined Index: User_Id. Where, User_Id Is Session Variable
How to Store .Pdf Files into MySQL as Blobs Using PHP
Unable to Upload File Greater Than 2Mb in Laravel
How to Pass Checkbox Value 0 If Not Checked and 1 If Checked Using Array Laravel
How to Count Occurrence of Duplicate Items in Array
How to Not Make Phpmailer Send an Email With Multiple 'To' Addresses
Php Regex Find Text Between Custom Added HTML Tags
Disable Xampp Redirect Http to Https
Returning Json from a PHP Script
PHP - Using Pdo With in Clause Array
Unset a Value in a Multi-Dimensional Array Based on One of the Values