PDO, Prepared statements and SQL

 

After reading several articles about PDO and MySQLi prepared statements, also already read tens of questions concerning prepared statements and SQL injection on stackoverflow.com , people were saying that with the correct use of prepared statements there's no need anymore to escape entries from users, But I think I am still worried having security concerns.

1st Question: If I still sanitize entries using reg-exp and escaping before using them in my prepared statements, is that like I'm over-taking it?

2nd Question: If prepared statements thing is doing the job concerning SQL-injection -From people comments and answers- why are there still compromised databases and more and more exposed data about credit cards numbers and passwords, hacked accounts even from "big" and well-known websites? does that mean prepared statement alone is not so immune, or it's a totally different topic?

If I still sanitize entries using reg-exp and escaping before using them in my prepared statements, is that like I'm over-taking it?

  • If you are removing characters with special meaning in SQL because they have special meaning in SQL, then that's a waste (and leads to storing Ms. O'Donnell's surname incorrectly)
  • If you are escaping those characters, then you will get double encoding, which is terrible as you'll end up (for example) sending emails starting with Dear Ms. O'Donnell,.
  • If you are making sure that a date is a sensible date, then that's just sensible protection of data integrity.

    • If prepared statements thing is doing the job concerning SQL-injection -From people comments and answers- why are there still compromised databases and more and more exposed data about credit cards numbers and passwords, hacked accounts even from "big" and well-known websites? does that mean prepared statement alone is not so immune, or it's a totally different topic?

    Because:

  • Not everybody uses prepared statements
  • Not everybody who uses them uses them correctly
  • Databases can be attacked through other vectors (such as a remote code execution vulnerability in a web server or brute force attacks on an admin user's SSH account).
  • If you are doing it aiming sql security only - yes, its obvious and useless overkill.
  • Yes, of course. As a matter of fact, native prepared statements covers only a fraction of cases, giving no security for others.

    • Nevertheless, the idea of a prepared statement in general is a brilliant one - so, a developer have to take care of the other cases oneself.

    Here is my solution - a library that offers a placeholder for the everything, not just two scalar data types only

  • Double escaping is wrong you don't need it. You just need to pass variables PDO will take care of rest.

  • Attacking site may be done on various ways. People don't always use prepared statements. There can be a lot of attacks like XSS, CSRF, the attacker may try to focus on server configuration.

    Basicly YOU NEED TO TAKE CARE OF EVERY VARIABLE THAT YOU MAY PUT IN DATABASE

    • Attackers are clever they try to find orginal ways to get to db. For example they can put xss in browser header of request and if you use statistic in your admin panel which show browser xss may work! That is why it is so imporant to take care of input. PDO does this job very well.

    To be sure everything is okay you should ask yourself a questions:

  • Is data I get the data I wanted? (use preg_match) for this.
  • Is it escaped against xss, csrf, sql injection?
  • Take a look at server's php and mysql configuration sometimes scripts are secured by other things are forgotten to be secured.
  • PDOStatement::bindValue() has an argument called data_type use it. If you want string use PDO::PARAM_STR if int PDO::PARAM_INT etc.

    • Quotation from php manual:

    Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.

    链接地址: http://www.djcxy.com/p/16756.html

    上一篇: 纯mysql预备语句防止注入攻击？

    下一篇: PDO，Prepared语句和SQL