In case of prevent SQLi happened I added this kind of check:

preg_replace("#([\[\]\|\.\,:'])#s", " ", $data);

This reg_ex should replace everything painfull, because the main query looks like

SELECT line FROM text WHERE tid LIKE '%{$data}%'

I put several encoded apostrophs trying to bypass my check and it seems pretty good protection. What do you think?

  • 1
    Why not use prepared statements, which are designed to segment the data and the query, and which are built into PHP's database libraries? – Matthew Nov 22 '16 at 13:19
  • 1
    YOu should really be using Prepared statements, or other "more secure" DB engines like PDO in PHP (note that some people claim its internal processing is better, not really sure how or why) – Lighty Nov 22 '16 at 13:22
  • That's a part of search request from my page. And in my search filed I want user to put exactly pattern, which they want to find. – Alberto Gouchinni Nov 22 '16 at 13:23
  • @AlbertoGouchinni Yes, but that would work perfectly well using prepared statements. Your way, you're modifying the search request by filtering out characters. With prepared statements, you'd pass through exactly what was supplied... – Matthew Nov 22 '16 at 13:25
  • Thank you! Will dig on it ! :) The situation is, that one of my customer already published that code and I'm afraid of SQLi. Sure I will modify my code up to your suggestion, but....is it possible to hack that regex or not?) – Alberto Gouchinni Nov 22 '16 at 13:30

There is no reason to implement your own sanitization logic in 2016. As Aria points out, you can just use mysqli_real_escape_string if you want to sanitize. The key here is that this is NOT a 100% effective means of preventing SQLi.

The proper way would be to use the mysqli library to parameterize queries, because this renders first order injection impossible! You might see a lot of recommendations to use PDO to make prepared statements. It's important to note that prepared statements are not the same as parameterized queries. Prepared statements are just statements passed to a module that does the sanitization for you.

That's not to say sanitization isn't useful, it just isn't useful for preventing SQLi. You should still do it, though, to prevent stored cross site scripting. Strip out anything that a browser might recognize as HTML or JS or CSS if you're storing any untrusted input that might be displayed in a page.

TL;DR Parameterize your queries to protect from SQLi; prepare them to protect from stored XSS.

EDIT: I just noticed that your regexp doesn't seem to account for semicolons or dashes, so I could achieve injection against it quite easily.

| improve this answer | |

This doesn't escape all characters required for safe insert of string into database.

You can escape $data with mysqli_real_escape_string on top of character removal to make sure it doesn't fail during insert.

Here is the list of characters.

| improve this answer | |
  • Hi! I don't need to insert string, I just building the search pattern. The dead situation - when user will try to insert apostrophe, which will close LIKE pattern specification in my query. – Alberto Gouchinni Nov 22 '16 at 13:24

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.