2

I'm studying SQL injection and tried in my PHP code this query:

$condition = str_replace(["'","\\"],["\\'","\\\\"], @$_GET['q']);
$query = "SELECT * FROM dummy_table WHERE dummy_column = '$condition'";

DB and tables charset is set to UTF8.

I can't inject anything, can someone help me please?

EDIT: As pointed out by GarethD this would escape first ' and than \, allowing injection, what about this str_replace?

$condition = str_replace(["\\","'"],["\\\\","\\'"], @$_GET['q']);
Dave
  • 39
  • 1
  • 4
  • 3
    What's wrong with using prepared statements / parameterised queries? – Jonnix Sep 09 '15 at 10:48
  • Looks at the output (the built query) and enable error reporting. Also once you stop studying it use prepared statements and bound parameters like @JonStirling said. – PeeHaa Sep 09 '15 at 10:48
  • And what values do `$condition` and `$query` have after executing the above? – Bob Jarvis - Слава Україні Sep 09 '15 at 10:50
  • 3
    As I wrote I'm studying SQL injection, so prepared statements are useless for me. I NEED a vulnerable query to train myself. I always use PDO and/or prepared statement when developing. Please stop suggest me other mitigations techniques and help me understand how to bypass THIS example. – Dave Sep 09 '15 at 10:52
  • Well I want to and I did. What was the outcome of it? – PeeHaa Sep 09 '15 at 10:54
  • 1
    @BobJarvis It depends on $_GET['q'] value, but every time I use a single quote it get translated to \'. How can I inject something in this query if I cannot use '? This should be a very basic example but I can't find an exploit. – Dave Sep 09 '15 at 10:56
  • Could we get some examples of $_GET variable, that can inject something, in this particular case? I think OP is looking for it. :) Not general story about prepared statements, etc, etc... – sinisake Sep 09 '15 at 10:57
  • 1
    @PeeHaa Result is always empty and I can't get mysql to throw an error, can you suggest me a value for $_GET['q'] throwing a mysql error? Error reporting is enabled and reports all (notice included) – Dave Sep 09 '15 at 11:00
  • 1
    You've said that "*DB and tables charset is set to UTF8*", but what about [the character set of the database connection](https://dev.mysql.com/doc/en/charset-connection.html)? Also, are you certain that the server's SQL mode does not include [`NO_BACKSLASH_ESCAPES`](https://dev.mysql.com/doc/en/sql-mode.html#sqlmode_no_backslash_escapes)? – eggyal Sep 09 '15 at 11:59
  • @eggyal All the charset settings are set to utf8. NO_BACKSLASH_ESCAPE is disabled. – Dave Sep 09 '15 at 12:09

1 Answers1

2

This isolated example is invulnerable to injection.

But you have to realize that protection from sql injection is not just a character replace. And circumstances may differ from ones you are taking at the moment for granted. So, your code would become vulnerable on the long run, due to essential drawbacks of this method:

  • character replace is only a part of required formatting
  • this particular replacement can be applied to strings only, leaving other parts absolutely unprotected.
  • such a replace is external to a query execution, means it is prone to a human error of any sort.
  • such a replace is an essentially detachable measure, means it can be moved too far away from the actual query execution and eventually forgotten.
  • this kind of escaping is prone to encoding attack, making solution too limited in use.

There is nothing wrong in character replacement per se, but only if it is used as a part of complete formatting; applied to the right query part; and done by a database driver, not a programmer; right before execution.

Functions you proposed in the comments are a good step, but still insufficient, being subjects of the drawbacks listed above, making them prone to all sorts of human errors.

And SQL injection is not the only problem with this approach, it is a usability fault as well, as this function would either spoil your data, if used as an incarnation of late magic quotes, or make your code bloated, if used to format every variable right in the application code.

Such functions can be used only to process a placeholder, but of course not by means of using a homebrewed replace function, but a proper function provided by database API.

Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    it would be good to explain what these essential drawbacks are. OP said he is studying it, so it will be good for him (and all of us also) – andrew Sep 09 '15 at 10:59
  • 1
    Your are saying that if I use something like: `function safeString($val){ $val = str_replace(["'","\\"],["\\'","\\\\"], $val); return "'$val'"; } function safeInt($val){ return is_numeric($val) ? (int)$val : 0; }` to escape any string or integer I would be invulnerable to SQL Injection for integer and strings? – Dave Sep 09 '15 at 11:12
  • Of course my function are called based on table column not user input. I know password must be a string so I'd use safeString not safeInteger. – Dave Sep 09 '15 at 11:49
  • @Your Common Sense It would be useful to list those "drawbacks" you talk about instead of simply post a generic SQLi article. – Dave Sep 09 '15 at 12:16