2

Can escaping functions (e.g. mysql_real_esacpe_string ) be moved down to the database layer where we would loop through all parameters passed for all queries and escape all strings. Would that be a good design?

Walter
  • 16,158
  • 8
  • 58
  • 95
Imran Omar Bukhsh
  • 1,959
  • 16
  • 25

3 Answers3

5

In most other languages you would use "prepared statements" for this where you separate the SQL from the values.

Doesn't PHP provide the same facility?

  • I think it does but never tried it? Is it really the professional way? – Imran Omar Bukhsh Apr 17 '11 at 08:21
  • 3
    Prepared statements are much more robust than generated SQL. –  Apr 17 '11 at 08:47
  • 3
    Not using prepared statements with user supplied data is inviting an SQL injection attack. Not using them in this case would be less professional than using them. – BillThor Apr 17 '11 at 17:50
  • @BillThor, careful escaping of user supplied data should be enough but this is what the OP wants to avoid. Personally I just find the dynamically generated SQL brittle and hard to test. –  Apr 17 '11 at 17:53
  • @Thorbjørn Ravn Andersen: Careful escaping of user supplied data should be enough, but tends to get missed often enough to be a problem. I do agree dynamically generated SQL is brittle and hard to test. – BillThor Apr 17 '11 at 18:06
  • 6
    **Use prepared statements to store data. period.** It's the only way to make sure you avoid SQL injection attacks where people can modify the price of products, drop your users table, or even the whole database if they are crafty. I don't think there is a language that uses databases that doesn't support prepared statements. They work. – Berin Loritsch Apr 18 '11 at 12:27
  • @Berin, no rules without exceptions. Prepared Statements may be too expensive to create for usage everywhere, and _no_ it is _NOT_ the only way. That is the whole idea of escaping strings. –  Apr 18 '11 at 12:47
  • @Thorbjorn, which is fragile at best. Escaping strings is about the easiest thing to break--both intentionally and unintentionally. Prepared statements are designed to be reused, making them much less expensive and providing other wins along the way. – Berin Loritsch Apr 18 '11 at 12:58
  • @Berin, less expensive _in the long run_ averaged over many requests. For one-shot SQL's they are not. –  Apr 18 '11 at 13:01
  • @user1249. Prepared statements do escape the string internally. You should program to the Prepared statement to avoid re-inventing the wheel or missing an edge case in your escapes. – mike30 May 13 '13 at 17:10
3

PHP does provide a good emulation of prepared statements through the built-in PDO library. Use this for SQL if you can. The mysql_* functions are quick, dirty and legacy.

Ben XO
  • 301
  • 1
  • 4
0

It would not be good design.

Use one of the common escaping libraries to escape the parameters.

Rolling out your own is error prone, especially in the database (where it might be subverted by a cleverly written parameter).

Additionally, SQL is fairly poor at string manipulation, so also a bad choice on this point.

Oded
  • 53,326
  • 19
  • 166
  • 181