How to bypass this silly SQL injection protection?

Question

I'm currently dealing with a SQL injection problem that involves an input being taken and put through a function that replaces all ' with \' and all \ with \\. I've tried a lot of things but I don't seem to be able to escape from the string literal.

Database encoding: UTF-8 (not GBK or any other vulnerable encoding, so the "%BF trick" won't work here)

Here's the sanitisation function and the SQL query:

function sillyEscape($s) {
    # Replace \ with \\ then replace ' with \'
    return str_replace('\'','\\\'', str_replace('\\','\\\\', $s));
}

$category = mysqli_query($conn, "SELECT * FROM categories WHERE name = '".sillyEscape($_GET['cat_name'])."';");

They always say "Don't make your own escaping function — you'll get it wrong." So my question is, did the person who created the sillyEscape function go wrong? And if so, how did they go wrong and how do I exploit this?

Again, the database encoding is UTF-8, so I'm not aware of any multibyte characters that we can inject to get out of this one.

Answer 1

That specific piece of code seems secure to me (it's essentially what eg PDO does when using emulated prepared statements). You can't use ' to exit the single-quoted string context, you can't use \ to escape ', and you can't escape \; without encoding issues such as GBK, that means you can't exit the string, and thus can't perform an injection attack.

The reason why it's strongly discouraged is that there are situations where it's not secure.

Three simple examples:

"SELECT * FROM categories WHERE id = ".sillyEscape($_GET['id']);
"SELECT * FROM categories WHERE id = \"".sillyEscape($_GET['id']).\"";
'SELECT * FROM categories WHERE id = "'.sillyEscape($_GET['id']).'"';   

And it's non-trivial to see if this is secure or not (especially when the queries get more complex and involve sub-queries stored in variables which are concatenated, etc).

"Don't insert user input into a query, bind it instead" is a simple rule that can easily be followed (in the vast majority of cases). "Use this escape function in a single-quoted string context, and some other mechanism in a different context" is more complex, and thus easier to get wrong.