Alternative to mysql real escape string without connecting to DB

Question

I d like to have a function behaving as mysql real escape string without connecting to database as at times I need to do dry testing without DB connection  mysql escape string is deprecated and therefore is undesirable  Some of my findings   http   www gamedev net community forums topic asp topic id 448909  http   w3schools invisionzone com index php showtopic 20064

User · Accepted Answer

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.

User · Answer

From further research  I ve found   http   dev mysql com doc refman 5 1 en news-5-1-11 html  Security Fix   An SQL-injection security hole has been found in multi-byte encoding processing  The bug was in the server  incorrectly parsing the string escaped with the mysql real escape string   C API function   This vulnerability was discovered and reported by Josh Berkus  and Tom Lane  as part of the inter-project security collaboration of the OSDB consortium  For more information about SQL injection  please see the following text   Discussion   An SQL injection security hole has been found in multi-byte encoding processing  An SQL injection security hole can include a situation whereby when a user supplied data to be inserted into a database  the user might inject SQL statements into the data that the server will execute  With regards to this vulnerability  when character set-unaware escaping is used  for example  addslashes   in PHP   it is possible to bypass the escaping in some multi-byte character sets  for example  SJIS  BIG5 and GBK   As a result  a function such as addslashes   is not able to prevent SQL-injection attacks  It is impossible to fix this on the server side  The best solution is for applications to use character set-aware escaping offered by a function such mysql real escape string     However  a bug was detected in how the MySQL server parses the output of mysql real escape string    As a result  even when the character set-aware function mysql real escape string   was used  SQL injection was possible  This bug has been fixed   Workarounds   If you are unable to upgrade MySQL to a version that includes the fix for the bug in mysql real escape string   parsing  but run MySQL 5 0 1 or higher  you can use the NO BACKSLASH ESCAPES SQL mode as a workaround   This mode was introduced in MySQL 5 0 1   NO BACKSLASH ESCAPES enables an SQL standard compatibility mode  where backslash is not considered a special character  The result will be that queries will fail   To set this mode for the current connection  enter the following SQL statement   SET sql mode  NO BACKSLASH ESCAPES     You can also set the mode globally for all clients   SET GLOBAL sql mode  NO BACKSLASH ESCAPES     This SQL mode also can be enabled automatically when the server starts by using the command-line option --sql-mode NO BACKSLASH ESCAPES or by setting sql-mode NO BACKSLASH ESCAPES in the server option file  for example  my cnf or my ini  depending on your system    Bug 8378  CVE-2006-2753   See also Bug 8303

User · Answer

In direct opposition to my other answer  this following function is probably safe  even with multi-byte characters      replace any non-ascii character with its hex code  function escape  value         return           for  i   0   i  lt  strlen  value      i             char    value  i            ord   ord  char           if  char          amp  amp   char           amp  amp   char           amp  amp   ord  gt   32  amp  amp   ord  lt   126               return     char          else              return       x    dechex  ord             return  return      I m hoping someone more knowledgeable than myself can tell me why the code above won t work

User · Answer

Well  according to the mysql real escape string function reference page    mysql real escape string   calls MySQL s library function mysql real escape string  which escapes the following characters   x00   n   r          and  x1a    With that in mind  then the function given in the second link you posted should do exactly what you need   function mres  value         search   array          x00     n      r                 x1a         replace   array           0     n      r                  Z         return str replace  search   replace   value

[php] Alternative to mysql_real_escape_string without connecting to DB

Examples related to php

Examples related to mysql

Examples related to mysql-real-escape-string