PDO, Prepared statements and SQL-Injection again

3
votes

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?

3
phppdoprepared-statementsql-injection
1) Yes. 2) sql injection isn't the only vulnerability a site can have. - itachi
See The Great Escapism (Or: What You Need To Know To Work With Text Within Text) for more conceptual answers. - deceze♦
@itachi, I do know there are several types vulnerabilities but i was asking about types those prepared statements suppose to eliminate the need to escape/sanitize - Mi-Creativity
@deceze, Thanks for that link, it summarizes lots of things. - Mi-Creativity

3 Answers

2
votes

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).
2
votes
  1. If you are doing it aiming sql security only - yes, its obvious and useless overkill.
  2. 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

2
votes

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

  2. 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:

  1. Is data I get the data I wanted? (use preg_match) for this.
  2. Is it escaped against xss, csrf, sql injection?
  3. Take a look at server's php and mysql configuration sometimes scripts are secured by other things are forgotten to be secured.
  4. 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.