SQL injection possible after preg_replace?

Question

I am just reviewing a script I want to use for my website. The author filters all user entered data ($_POST and $_GET) with this function:

function XSSCheck($value) {
    return preg_replace(
        array('/&(?!amp;|quot;|nbsp;|gt;|lt;|laquo;|raquo;|copy;|reg;|#[0-9]{1,5};|#x[0-9A-F]{1,4};)/', '/#(?![0-9]{1,5};|x[0-9A-F]{1,4};)/',       '|<|',  '|>|',  '|"|',      "|'|"   ),
        array('&amp;', '&#35;', '&lt;', '&gt;', '&#34;', '&#39;'),
        stripslashes($value)
    );
}

If I filter a string with that, is it possible to inject into this SQL query for example?

SELECT * FROM table WHERE ID = '{$_REQUEST['id']}'

I tried it already but did not find out any way to do so. Does anybody know an alternative or is this code secure?

If it is secure, what are the (dis)advantages to mysqli_real_escape_string()? Should I change the projects code to the official function?

EDIT: You voted down my question, request to close it and sayed the code is bad. But no one has given me any example, how code can be injected! I do not think there is a way.

Yes, it is very possible. That code is *not* for preventing SQL injection. — John Conde, Sep 19 '15 at 16:31
@JohnConde: Why? I think the escape character is prevented... — Richard, Sep 19 '15 at 16:33
You're using MySQLi, so move into the 21st century and use prepared statements/bind variables to prevent SQL injection — Mark Baker, Sep 19 '15 at 16:34
@MarkBaker: No, **I** am not using MySQLi - the author of the script is :-) — Richard, Sep 19 '15 at 16:35
Value sanitizing is not the best [approach to prevent SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). AFAIK, it should be done with DBMS-driver `escape*` function or. Better approach I might suggest it to use prepared statements. — BlitZ, Sep 19 '15 at 16:35
@MarkBaker: The function is only named like this - do not know why ;-) — Richard, Sep 19 '15 at 16:35
You specifically mention.... `mysqli_real_escape_string()`! That suggests you're using MySQLi — Mark Baker, Sep 19 '15 at 16:36
You voted down my question, request to close it and sayed the code is bad. But no one has given me any example, how code can be injected! I do not think there is a way. — Richard, Sep 19 '15 at 16:51
RichardReiber, this is because that is a problem solved very long time ago, if you would refer to the [question](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) that @Num6 provided in the comments of my answer, you will see many examples. — php_nub_qq, Sep 19 '15 at 16:58
@php_nub_qq: I do not find a injection method for bypassing a `'` filter in a string... — Richard, Sep 19 '15 at 17:00
@RichardReiber still, read [this](http://stackoverflow.com/a/12118602/2269749). — BlitZ, Sep 19 '15 at 17:01
@Num6: What do you want from me? An attacker cannot use this, because he is in a string, which is started by `'`. In my opinion, there is still no possibility to bypass this. `latin1` is used and MySQL is configured safe. Where the heck should someone inject someone something when using this code? — Richard, Sep 19 '15 at 17:07
If you want to believe that your code is safe, then that's your prerogative.... I just hope any clients that you have realise otherwise.... but be aware that it's possible to SQL inject without injecting a quote into the value — Mark Baker, Sep 20 '15 at 01:23
It's also bad to use `{$_REQUEST['id']}` because you don't know whether the value came from the URL, POST vars, a cookie or where — Mark Baker, Sep 20 '15 at 01:23
If you have two parameters like `SELECT * FROM table WHERE a='$a' AND b='$b'`, you can do this: ```a=\\```, `b= OR 1=1-- `, which results in `SELECT * FROM table WHERE a='\' AND b=' OR 1=1-- '` — Gumbo, Sep 20 '15 at 06:42
[Same question on Security.SE](http://security.stackexchange.com/q/100662/539) — Gumbo, Sep 20 '15 at 06:56

score 2 · Answer 1 · answered Sep 19 '15 at 16:37

2

The only way to surely avoid SQL injection (a problem that is older than me) is prepared statements. Any other "escapes" have holes (many theoretical) and should be avoided.

Anyone who is serious about their security will use prepared statements (and not emulated), using "escapes" and such functions as the one you have shown is unprofessional and inconsiderable IMHO.

answered Sep 19 '15 at 16:37

php_nub_qq

15,199
21
74
144

1

Well, if driver-based `escape` function does not guarantee value safeness, then what will? – BlitZ Sep 19 '15 at 16:39
@Num6 I'm afraid I'm not the one who can answer this questions. It is not my fault there are security holes in escaping mechanisms, nor can I do anything about it. – php_nub_qq Sep 19 '15 at 16:40
@Num6 it is quite an interesting topic to learn, to be honest. If you are not familiar with it I'd suggest looking into character encodings and how they are being used to bypass escaping functionalities, it is pretty hard to accomplish and on a very deep level but is possible. – php_nub_qq Sep 19 '15 at 16:41
Well, there is still [possibilites](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string), yeah. Thanks. – BlitZ Sep 19 '15 at 16:50
@Num6 The 2nd answer in the question you linked explains what I meant. – php_nub_qq Sep 19 '15 at 16:52

SQL injection possible after preg_replace?

1 Answers1