3

My code is:

SELECT * FROM tlb_members WHERE username="{mysqli_real_escape_string($_GET['test'])}"

I'm searching and some people said using quotes in SQL statements is safe. Is this true?

3
8

It's mostly safe, except in some corner cases regarding certain character sets. Still, it's not the correct approach.

But first off, PDO with default configuration also suffers from the corner cases described above.

There are still very good reasons not to do it like this though:

  • theoretically, it just makes more sense to separate user input and SQL keywords instead of escaping.
  • it's generally recommended to use prepared statements, not to escape. It's not always good to go with general recommendations, but if you go against them, you should have a good reason for doing so.
  • it's harder to mess up prepared statements than escaping, because you have to do it right when you bind the data to the query, while escaping can take place anywhere (and often does, to avoid duplication, etc).
  • prepared statements always uses the same approach, just bind the data. With escaping, you have to remember to have quotes, or to cast to int, depending on the data you have.
  • quotes are very easy to forget and to overlook, especially in more complex queries.
  • it seems to me, that by using prepared statements, you focus on security, while when using escaping, it's more of a side-thought that gets in the way of the task you really want to accomplish, making it easy to forget to add escapes or quotes just once (you can't forget to bind the variables, as then it wouldn't work).
  • prepared statements are nicer to read than code that has mysqli_real_escape_string all over the place.
  • it's a lot easier to find weaknesses in your code if you use prepared statements (just grep for queries that contain variables).
7

You do not properly sanitize the HTTP GET parameter. The only good way to prevent SQL injection is to use parameterized queries.

Please have a look at MeekroDB: http://meekro.com/ this should help you in creating rather safe queries and to me it is also a lot easier to use.

Keep in mind that what is considered to be sufficiently safe today, could change over night. There's no such things as 100% security.

6
  • Im using combo mysqli_real_escape_string and quotation. So it is already safe ?
    – Anh
    Mar 7 '16 at 6:48
  • 1
    Why are you trying to reinvent the wheel when there are secure database frameworks out there?
    – Jeroen
    Mar 7 '16 at 6:49
  • 1
    that lib need to buy and i have the mysqli_class already. im just worry abount this security
    – Anh
    Mar 7 '16 at 6:58
  • 2
    So it is already safe ? No. stackoverflow.com/a/12118602/3134621
    – deviantfan
    Mar 7 '16 at 7:21
  • @Jeron. Some people want to learn HOW to do it, before jumping right to someone else's code. Copy and paste doesnt teach you anything. Dec 21 '20 at 0:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.