4 ms·
while I understand that sql injection is mostly a fault of the host programming language/developer (php in this case) and not of the dbms/dba, couldn't the latt
by riffraff 16y ago
while I understand that sql injection is mostly a fault of the host programming language/developer (php in this case) and not of the dbms/dba, couldn't the latter have avoided this in part by limiting user privileges so that it was impossible to "list the internal databases, tables and password dump" e.g. "REVOKE SHOW DATABASES, SHOW VIEW" ?
(I'm aware this may make impossible to use some web frameworks which rely on rdbms reflection, but I have the feeling this is not the case)
- m0nastic 16y agoSQL Injection isn't even really an issue with the host programming language. It's an issue with processing input that's submitted "as is". Aside from implementing bound parameters (which is useful for more than just preventing sql injection), we usually recommend using stored procedures if at all possible. It's really suboptimal to rely on the user to specify what table they're accessing.
- wulczer 16y agoAbsolutely. The web application should be logging to the database as an unpriviledged user. Seems that by now most people have learned that running web services as root is a bad idea, but they still forget that they should also have an unpriviledged database user for them.
- deleted 16y ago[deleted]
- seldo 16y agoIf you're using a web framework which is constantly querying the database to determine table structure, then you have two problems.
- kprobst 16y agoThe database in this case has to share some of the blame. For years MySQL did not support parametrized queries or stored procedures (or did not promote them), and many of the people coding against it consistently claimed that they didn't need that kind of "enterprise crap" polluting their favorite DB. This gave way to a MASSIVE code base that is inherently vulnerable to injection attacks. So yeah, it's the fault of the developers but it's also the fault of the people who aggressively marketed and evangelized MySQL and helped create the ditch they're just now digging themselves out of. It's a bit like the VB/Access culture Microsoft promoted back in the day, which generated some of the most hideous corporate apps I've ever seen. Yes, some developers are bad, but the company or group doing the evangelizing/marketing also needs to share some responsibility.
- FooBarWidget 16y agoYou don't need parameterized queries or stored procedures for protection against injection. In fact they're not injection protection mechanisms at all. What you actually mean is that the database libraries should provide parameterized-style APIs. There's no need for the database's native wire protocol to explicitly support parameterization to implement this. For example Ruby on Rails's ActiveRecord provides an API that looks a lot like it uses parameterization under the hood: BlogComment.find(:all, :conditions => ['email = ?', 'foo@bar.com']) But if you look at the source code you'll see that it just constructs a normal SQL query by internally substituting the '?' with the escaped version of 'foo@bar.com'. It does not utilize the database parameterization APIs at all. If you think stored procedures protect you against SQL injection, consider the following code snippet: sql_exec("CALL InsertBlogComment('" + get_parameter("text") + "')")
- awj 16y agoYou're arguing against the periphery of his point. By waiting so long to provide parameterized queries, MySQL helped foster the attitude that they were "useless enterprise bloat". The lack of parameterized queries was likely an influencing factor in the non-support or non-advertisement of parameterized-style APIs. MySQL is a crucial part of its own community. You cannot hold the community responsible for this situation while giving MySQL a pass.
- tptacek 16y agoI have never heard someone say parameterized queries were "useless enterprise bloat". Their use in modern web apps is an industry best practice widely adopted across all the Internet apps Matasano gets to test. Your reaction here sounds hyperbolic, and the parent commenter is right: parameterized queries, while helpful, are neither required nor sufficient for defense against SQLI.
- nupark 16y agoCould you expand a bit on how parameterized queries are not sufficient for defense against SQL injection (assuming, of course, that developers use the escaping and do not concatenate unescaped data into queries)? As for them being required -- you can obviously escape queries yourself, but the normative reference for escaping is the target database itself, and reproducing escaping locally in the client brings with it the likelihood of introducing an error in the custom implementation.
- xd 16y agoSorry, but how is PHP at fault? You can shoot yourself in the foot with ANY programming language.
- Natsu 16y agoPHP has been known to provide convenient footguns in the past (e.g. register_globals, mercifully depreciated), so it's not surprising that security-minded people give it a hard time. Think of it as the difference between the language keeping loaded footguns under its pillow with the safety off and keeping unloaded footguns in a locked gun safe. One is a lot less likely to get used than the other, even if either one will shoot your foot just as well.
- xd 16y agoRehashing old design floors is not an excuse to blame PHP for programmer error.
- prodigal_erik 16y agoIt doesn't seem fair to call a defect "old" if it wasn't seriously addressed between then and now. I had to pick up PHP (presumably because of heinous sins in a past life) and every tutorial I saw was still pasting user input into non-parameterized queries. There are apparently several different MySQL clients, and our production boxes still had the original (inexplicably still in existence) which didn't even support parameterized queries. And that was in 2007!
- damncabbage 16y agoJust hit the same issue here in 2011. Plesk, a popular package for managing shared hosting used by hosting companies, doesn't include the MySQL drivers for PDO (what's meant to be PHP's "standard" database library). Last week I had to rewrite an import script to use mysql_query(), with mysql_real_escape_string() and quotes for every query variable.
- sausagefeet 16y ago