Viewed   161 times

Is there an SQL injection possibility even when using mysql_real_escape_string() function?

Consider this sample situation. SQL is constructed in PHP like this:

$login = mysql_real_escape_string(GetFromPost('login'));
$password = mysql_real_escape_string(GetFromPost('password'));

$sql = "SELECT * FROM table WHERE login='$login' AND password='$password'";

I have heard numerous people say to me that code like that is still dangerous and possible to hack even with mysql_real_escape_string() function used. But I cannot think of any possible exploit?

Classic injections like this:

aaa' OR 1=1 --

do not work.

Do you know of any possible injection that would get through the PHP code above?



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";
Tuesday, August 9, 2022

There are a few cases where this escape function will fail. The most obvious is when a single quote isn't used:

string table= """ + table.Replace("'", "''") + """
string var= "`" + var.Replace("'", "''") + "`"
string index= " " + index.Replace("'", "''") + " "
string query = "select * from `"+table+"` where name=""+var+"" or id="+index

In this case, you can "break out" using a double-quote, a back-tick. In the last case there is nothing to "break out" of, so you can just write 1 union select password from users-- or whatever sql payload the attacker desires.

The next condition where this escape function will fail is if a sub-string is taken after the string is escaped (and yes I have found vulnerabilities like this in the wild):

string userPassword= userPassword.Replace("'", "''")
string userName= userInput.Replace("'", "''")
userName = substr(userName,0,10)
string query = "select * from users where name='"+userName+"' and password='"+userPassword+"'";

In this case a username of abcdefgji' will be turned into abcdefgji'' by the escape function and then turned back into abcdefgji' by taking the sub-string. This can be exploited by setting the password value to any sql statement, in this case or 1=1-- would be interpreted as sql and the username would be interpreted as abcdefgji'' and password=. The resulting query is as follows:

select * from users where name='abcdefgji'' and password=' or 1=1-- 

T-SQL and other advanced sql injection techniques where already mentioned. Advanced SQL Injection In SQL Server Applications is a great paper and you should read it if you haven't already.

The final issue is unicode attacks. This class of vulnerabilities arises because the escape function is not aware of multi-byte encoding, and this can be used by an attacker to "consume" the escape character. Prepending an "N" to the string will not help, as this doesn't affect the value of multi-byte chars later in the string. However, this type of attack is very uncommon because the database must be configured to accept GBK unicode strings (and I'm not sure that MS-SQL can do this).

Second-Order code injection is still possible, this attack pattern is created by trusting attacker-controlled data sources. Escaping is used to represent control characters as their character literal. If the developer forgets to escape a value obtained from a select and then uses this value in another query then bam the attacker will have a character literal single quote at their disposal.

Test everything, trust nothing.

Tuesday, November 29, 2022

I believe there are three different cases that you have to worry about:

  • strings (anything that requires quotes): '''' + replace(@string, '''', '''''') + ''''
  • names (anything where quotes aren't allowed): quotename(@string)
  • things that cannot be quoted: this requires whitelisting

Note: Everything in a string variable (char, varchar, nchar, nvarchar, etc.) that comes from user-controlled sources must use one of the above methods. That means that even things you expect to be numbers get quoted if they're stored in string variables.

For more details, see the Microsoft Magazine (Obsolete link: 2016-10-19).

Here's an example using all three methods:

EXEC 'SELECT * FROM Employee WHERE Salary > ''' +
     REPLACE(@salary, '''', '''''') +   -- replacing quotes even for numeric data
     ''' ORDER BY ' + QUOTENAME(@sort_col) + ' ' +  -- quoting a name
     CASE @sort_dir WHEN 'DESC' THEN 'DESC' END     -- whitelisting

Also note that by doing all the string operations inline in the EXEC statement there is no concern with truncation problems. If you assign the intermediate results to variables, you must make sure that the variables are big enough to hold the results. If you do SET @result = QUOTENAME(@name) you should define @result to hold at least 258 (2 * 128 + 2) characters. If you do SET @result = REPLACE(@str, '''', '''''') you should define @result to be twice the size of @str (assume every character in @str could be a quote). And of course, the string variable holding the final SQL statement must be large enough to hold all the static SQL plus all of the result variables.

Friday, November 11, 2022

This answer depends on the driver you are using.

Erlang ODBC has a function param_query that binds a set of parameters to the query and it might also escape all the SQL special characters.

erlang-mysql-driver has prepared statements:

%% Register a prepared statement
              <<"UPDATE developer SET country=? where name like ?">>),

%% Execute the prepared statement
mysql:execute(p1, update_developer_country, [<<"Sweden">>,<<"%Wiger">>]),

(code from Yariv's blog)

As a last resort you can always escape the characters

 NUL (0x00) -->  
 BS  (0x08) --> b
 TAB (0x09) --> t
 LF  (0x0a) --> n
 CR  (0x0d) --> r
 SUB (0x1a) --> z
 "   (0x22) --> "
 %   (0x25) --> %
 '   (0x27) --> '
    (0x5c) --> \
 _   (0x5f) --> _ 
Friday, November 18, 2022

Yes, I would say it's good practice to have users connect using accounts that only allow the least privileges they need to use the site. If your web users should only be reading data from the database then I would definitely create an account that only has read access and have them hit the DB through that.

The more important thing would be to secure your web application. You can still be victim of a devastating SQL Injection attack even if a user does not write to your database (think stolen credit card numbers or passwords).

Monday, August 29, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :