Does eliminating dangerous characters avoid SQL-injection?

Question

Avoiding SQL-injections there are many ways How to prevent SQL injection in PHP?.

The question is, how is it possible to sql-inject through removeBadCharacters?

function removeBadCharacters($s)
{
   return str_replace(array('&','<','>','/','\\','"',"'",'?','+'), '', $s);
}

It tries to throw out dangerous characters (the application doesn't need those characters):

$x = removeBadCharacters($_POST['data']);

mysql_query("insert into table (x) values ('".$x."');");

// or

mysql_query("select * from into where name = '".$x."';");

score 9 · Accepted Answer · edited May 23 '17 at 12:32

To be able to inject arbitrary SQL from the context of a string literal, that string literal needs to be left. This is only possible by introducing a string end delimiter, in this case a single ', or by expand the a string literal to a preceding ', e.g., by using the escapes character \:

$a = '\\';
$b = ' OR 1=1 OR ';
$c = ' --';

$query = "SELECT * FROM t1 WHERE a='$a' AND b='$b' AND c='$c'";
// result:
// SELECT * FROM t1 WHERE a='\' AND b=' OR 1=1 OR ' AND c=' --'
//                          \_________/           \_______/

Now as your function removes any ' and \, it seems to be impossible to leave or expand the string literal and thus not possible to inject arbitrary SQL.

However, since your function does not take the actual character encoding into account, it is possible to exploit this if the MySQL’s character encoding is GBK, similar to how it can be exploited when using addslashes instead of mysql_real_escape_string:

$a = "\xbf";
$b = " OR 1=1 OR ";
$c = " --";

$query = "SELECT * FROM t1 WHERE a='$a' AND b='$b' AND c='$c'";
// result:
// SELECT * FROM t1 WHERE a='縗 AND b=' OR 1=1 OR ' AND c=' --'
//                          \_________/           \_______/

So to play safe, use mysql_real_escape_string or other proven methods to prevent SQL injections.

You cannot tell for the whole application but for the 2 given queries only. — Your Common Sense, Jun 01 '13 at 14:41
@YourCommonSense This applies to all injection points inside a SQL string literal. — Gumbo, Jun 01 '13 at 14:48

score 5 · Answer 2 · edited Jun 20 '20 at 09:12

5

The very idea of removing whatever characters is utterly wrong.

That's what essentially wrong your approach.

You have to format your SQL literals properly instead of spoiling them.

Imagine this very site were using such a "protection": you'd were unable to post your question!

To answer your question literally - yes, under some circumstances it's very easy to inject. Just because the very idea of all-in-once sanitization is broken. PHP had a similar feature once, called "magic quotes". It was a hard lesson, but now it's got removed from the language at last. For the very reasons I told you:

it does not make "data" "secure"
it spoils your data instead

Every SQL literal have to be treated personally, according to its role.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 01 '13 at 13:16

Your Common Sense

156,878
40
214
345

4

Sorry, I do not discuss what particular toe you wish to shoot out. You just shouldn't shoot yourself in a leg. Period. – Your Common Sense Jun 01 '13 at 13:22
@MM. Inputs with dollar signs? – Mr Lister Jun 01 '13 at 13:26
1

@MM. The problem is your question and not this answer. You wrote: `I wrote it many years ago and I'm not going to change it` -> answer, then you'll be vulnerable! use the well known approaches like prepared statements. – hek2mgl Jun 01 '13 at 13:26
1

@hek2mgl: Yes, it's vulnerable. I'm interested in _how_? – masoud Jun 01 '13 at 13:39
https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string @deepmax Prepared statements are superior to mysqli_real_escape. Yes, it usually does the job, but it's not as safe as prepared statements. – Gall Annonim Jul 24 '19 at 07:34

score 2 · Answer 3 · edited May 23 '17 at 12:32

2

Your function doesn't deal with different encodings.

Don't try to come up with sanitation methods yourself, use something already made. In the case of mysql_*, it would be mysql_real_escape_string, however, you shouldn't use mysql_* anymore, use PDO or mysqli instead.

See: How can I prevent SQL injection in PHP? for further details.

edited May 23 '17 at 12:32

Community

1
1

answered Jun 01 '13 at 13:13

Femaref

60,705
7
138
176

score 2 · Answer 4 · edited Mar 17 '17 at 10:45

2

Yes, this can be defeated using Unicode characters and manipulating character sets.

See https://security.stackexchange.com/questions/11391/how-did-anonymous-use-utf-16-ascii-to-fool-php-escaping for further details.

edited Mar 17 '17 at 10:45

Community

1
1

answered Jun 01 '13 at 13:13

ose

4,065
2
24
40

Does eliminating dangerous characters avoid SQL-injection?

4 Answers4

The very idea of removing whatever characters is utterly wrong.

Linked