-1

So I was messing around with SQL injections on my website, trying to find an easy way to protect it from SQL injections. I found many things about "Prepared SQL statements", mostly in this post.

This is all nice and yes, it does prevent any kind of SQL injection I know, and also prevents all applications made for testing the security from SQL injecting.

However, I found one thing in what all injections are the same, and that is using ' quotes. My question is: why use prepared statements or something like that, when I could simply prevent query from being run using htmlspecialchars?

Example:

$id = htmlspecialchars($_GET['id'], ENT_QUOTES);
$query = Mysqli_Query($dbc, "SELECT * FROM `Users` WHERE `user_id`='$id'") or die(mysql_error());

This prevented all the injections by changing the ' character into &#039, which is invalid in MYSQL statements.

Am I missing something in here? Is there a reason why people use prepared statements instead?

Community
  • 1
  • 1
Eda190
  • 669
  • 1
  • 7
  • 20
  • 1
    good read http://stackoverflow.com/a/12202218/3859027 – Kevin Mar 05 '15 at 12:08
  • 1
    We get a question asking "why should I use prepared statements" every couple of weeks, and the answer remains the same: it is a tried and tested mechanism which should be used wherever it is available. Trying to "do it better" is invariably not easier (parameterisation is so easy anyway) and you might miss a security issue. – halfer Mar 05 '15 at 12:10
  • 2
    In any case, `htmlspecialchars()` is not the correct escaping function for SQL - if you won't use bound variables, you should at least use the escape function provided by your database library instead. – halfer Mar 05 '15 at 12:11
  • possible duplicate of [A few questions about PDO and prepared statements](http://stackoverflow.com/questions/5020905/a-few-questions-about-pdo-and-prepared-statements) – halfer Mar 05 '15 at 12:15
  • I forgot to mention, that my test website IS using UTF-8 charset. I did read the post @Ghost mentioned, but I would expect answer saying "HOW could my code be exploited", not "WHY should I use prepared statements". Thank you anyways – Eda190 Mar 05 '15 at 12:16
  • ...because then your data will contain garbled HTML entities instead of quotes...!? That's reason enough for me. – deceze Mar 05 '15 at 13:12
  • What if I dont want any forms of quotes in my GET values? Ofcourse, prepared statements make it possible to send EVERYTHING I dream off throught GET, but if I only need plain text values...it is not necessary – Eda190 Mar 05 '15 at 13:18
  • 2
    *If* A and B and C and probably D *then* just maybe it may work... or you could use a solution which has no ifs and buts at all and just works. Your choice. – deceze Mar 05 '15 at 13:23

2 Answers2

4

Parameterised queries are a database feature; the database itself offers an API to take the query and its data separately. This leaves zero chance of anything going wrong*. Compare that to any string encoding/slicing/replacing operation you do in PHP, in the end you'll still be sending one long string to the database. You will have to think of Every. Possible. Combination. of characters which may escape (no pun intended) your sanitisation efforts. You will also have to know every possible combination of escape sequences that MySQL understands, which may lead to unexpected results. You've escaped ', great. How about I'll insert a \ as the last character in my string? Then your query will be:

INSERT INTO foo VALUES ('bar"\')

Oops.**

mic drop

* You can of course still shoot yourself in the foot any number of ways, but at least in terms of escaping, it's solid.

** If you cannot see the issue with this query, see the comments below.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • This is the answer I needed. Thank you. I do understand how parameterised queries work, I'm just curious about other solutions, that I find easier and nicer in the code structure. – Eda190 Mar 05 '15 at 13:27
  • I think you're really just saying that you find **mysqli's API** for binding parameters disagreeable. And that's absolutely correct, it's pretty bad. Try PDO instead, its API is a lot better. You can do the same thing in the same two lines you currently have. – deceze Mar 05 '15 at 13:29
  • I didn't yet read anything closer to PDO, I just find Mysqli clearer to understand, so I'm using it. I have many things to learn, and I'm trying hard to do that. :) – Eda190 Mar 05 '15 at 13:33
  • @Luis This query will *not* be "executed without any issues". This particular query will generate a syntax error. (It's hard to differentiate, but the last `)` is red, when it should be black). But it's really supposed to demonstrate how it opens up yet another possible injection vector by invalidating/circumventing string quotations, even though the given quote is "escaped". Which is supposed to say *it ain't all that easy, there are more things to think about than you are thinking about, can you really ever be sure you've thought about everything?* – deceze Mar 05 '15 at 15:33
  • sorry, my bad, I have tested against sqlite, your example is correct, it fails! – Saic Siquot Mar 05 '15 at 17:14
1

Well htmlspecialchars() protects you in the same way this str_replace("'",'',$_GET['id']) do. They both alter the original data in a way that they don't preserve the data "as is". Even more, as they are not the intended functions for this work, same encodings, character sets (or other factors, I don't know) may defeat the "protection". If you do not want to use prepared statments the correct function is mysqli_real_escape_string (or it's similar OOP)(Please notice the "i" on the name) also notice there, that even this function needs caution about character set. Two side notes: For an expected integer, it is enough to stop injection: $value = (int)$_GET['id'], to prevent against XSS injection use htmlspecialchars

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
Saic Siquot
  • 6,513
  • 5
  • 34
  • 56
  • Yes, sorry, was an extremist comparison. My point was 1- preserve data as is on the DB, 2-show the correct function to the OP – Saic Siquot Mar 05 '15 at 13:25
  • I will take a look at real_escape_string thing, but you didn't infact answer my question. Also I allready use int check, when expecting number as the only valid character, but I will try to look for a function that will check if the ID contains only plaintext valid in UTF-8, and that should solve my question. – Eda190 Mar 05 '15 at 13:40
  • Why I didn't answer your question? it is because I don't have an example where htmlspecialchars() fails? yes true. But be sure, I won't search that case also. perhaps works but may fail, becouse is not for this purpose. But in any case it do not preserve data as is. Enough to discard it. I point to you to the function that is the direct replacement to the wrong htmlspecialchars, that will work whitout any issues. That is the spirit of my anwser. – Saic Siquot Mar 05 '15 at 13:56
  • I gave you an upvote, but you didnt provide example like deceze did, so his answer is "better". Still thank you for your time, and I asure you, I will use real_escape_string instead of htmspecialchars in this case. :) – Eda190 Mar 05 '15 at 14:10
  • I also gave you my up vote, it is not the point (but thanks), I didn't think an example was needed. It is as simple as where you written htmlspecialchar, write mysqli_real_escape_string (and read about charcter set there) – Saic Siquot Mar 05 '15 at 14:18