For a long time I have been tormented by the question: How to protect your site from SQL-Injection. In the old days, I used mysql_real string_escape to escape quotes. But some time has passed, and I learned about PDO , they say, they say, this topic will help well in protecting the site. Say, I am not a great hacker and I can’t verify it in any way, I know that with mysql_real string_escape all quotes and other nonsense were screened - and voila, you are already in the house.

Now I am working on my project and decided to check the site for penetration protection. As a result, what I did:

I just took and entered data with quotes, for example, Infor`mat'ion. As I have already said, I am not a great hacker, but I know for sure that all this rubbish should be screened, but it was so entered into the database, which prompted me to think that I don’t have any protection at all. And I entered the data like this:

 $sql = "INSERT INTO chat (time, login, private, text) values(?, ?, ?, ?)"; $db->prepare($sql)->execute(array($time, $login, $private, $text));`

I read this method on a habre in "Why you should use PDO to work with a database," the connection is the same as indicated in the article.

How can you protect against these injections?

  • @IamS I added a little response. Perhaps you or the community will be interested to know what other measures you can / should take in order to “sleep well”. - romeo
  • 2
    Conclusion you made the wrong, shame and all the problems. но точно знаю, что вся эта дрянь должна экранироваться, но она так и заносилась в базу данных . This rubbish was screened out, so that it would be recorded in the database in the form that is and did not affect the execution of the query, including, did not change its behavior, thereby squeezing SQL injections. Those. Shielding did its job, exactly what the user entered into the database. Now, if your query was broken off, it would be very bad, because it allowed inserting into the text a piece of SQL to execute it. - Vitalts

3 answers 3

When using prepared statements, the parameters of external origin are sent to the server separately from the request itself, or are automatically escaped by the client library.

Create a drop_table table. Further

 $name = "Evil'); DROP TABLE drop_table;--"; $sql = "INSERT INTO `test` (`name`) values('{$name}');"; $statement = $db->prepare($sql); $statement->execute();

The drop_table table drop_table successfully deleted. :)

Now the same request, but only through the prepared statement:

 $sql = "INSERT INTO `test` (`name`) values(?);"; $statement = $db->prepare($sql); $statement->execute([$name]);

In the test table added entry Evil'); DROP TABLE drop_table;-- Evil'); DROP TABLE drop_table;--

UPDATE

Thus, we protected ourselves from SQL injection , but did not protect against XSS .

In the latter case it is required:

  • validation . For example, if it is necessary to notify the user about the inadmissibility of the data entered by him or for logging;
  • sanitization . Converting data to the appropriate type (int)$age . In PHP, there is a set of filters created for just such cases;
  • use of lightweight markup language . Markdown - it is them that you use on the hashcode, or you can use the old bbCode . Ideally, force not only an ordinary user, but also the site administration to use it. In order to optimize, (so as not to parse each time), you can use key-value storage (memcached, redis, ...) to store the html version.

If for some reason you want to store in the html database from a source that does not cause trust, then you can use HTML Purifier .

Some advertising: :)

    That's right: the line “Infor`mat'ion” should be added to the table. Escaping should occur at the time of the request, and not be stored in this form. And PDO copes with it perfectly. And mysql_real_escape_string (and mysqli_real_escape_string ) too. And even str_replace is also a way to protect yourself. (See UPD) The new thing that PDO gives us is monitoring the types of data transferred and the distinction between action and data. A popular mistake that led to disastrous results was that some people forgot to check non-string types:

     $q = "SELECT * FROM `table` WHERE `id` = ".$_GET['post_id'];

    The difference is obvious:

     $q = "SELECT * FROM `table` WHERE `id` = ".intval($_GET['post_id']);

    Or just always process the input data, watch for quotes, possible representations of numbers, representations of bool data ... Or just rely on a ready-made wrapper, for example, PDO. :)

    UPD: WARNING!

    mysqli_real_escape_string (probably), mysql_real_escape_string , and even more so str_replace - are unsafe (\ xbf \ x27 and other problems), use MySQL only in the mode of real prepared queries! PDO needs to be used correctly (thanks to @romeo for the link).

    • PS By the way, I'm wondering: PDO behaves as if it sends a MySQL request and associated parameters separately. Is that so, or does he still form a regular request on his side? - Bars
    • @Bars, can both cases, as far as I know. - etki
    • 2
      @Bars PDO by default emulates this behavior for all engines, including those that do not need it (for example, mysql of these). To read . - romeo
    • @romeo, thank you very much for reading, I just recently encountered an error that was a consequence of emulation, and could not understand what was going on: $ stmt = $ pdo-> prepare ("SELECT user_id FROM users LIMIT: limit"); $ stmt-> bindValue ('limit', 10); $ stmt-> execute (); / * ... I guess I specified the type (did ORM, so my system allowed it): $ stmt-> bindValue ('limit', 10, PDO :: PARAM_INT); But now I know the root cause of the problem, thanks. - Bars

    PDO, and its prepare.

     $temp = $mysql->prepare('DELETE FROM `unigies` WHERE `idUser` = :idUser Limit 1;'); $temp->bindParam(':idUser', $id, PDO::PARAM_INT); $temp->execute();