I've been thinking briefly about database access in PHP scripts, how they are exploited and such.
One thought of mine was to create several different users for a database, each with different levels of control (read, write etc) and only use the appropriate one for each individual query.
A good idea, a good idea but too much work or just a completely rubbish idea?
It'd barely provide any benefit for effort. As long as your code is secure, doing this is simply going to over-complicate everything for you. If your code isn't secure (which I doubt for you, mathiaus), then that's what you should be working on that instead.
If you make one mistake in one page that only needs to get a row of data from one table, and the hacker somehow manages to inject sql you'd be glad you only gave the user read access to one table.
I think it's a lot saver, but not sure if it's worth the trouble.
and hogwarts, everyone makes mistakes. Better to think you make mistakes and not make them, than the other way around.
|rvec wrote: |
|and hogwarts, everyone makes mistakes. Better to think you make mistakes and not make them, than the other way around. |
Abstraction layer, Rvec
Additionally, any programmer worth half their arse can and does avoid them anyway. They're incredibly easy to stop and you'll only find such exploits in software like PHP-Nuke. Other security remember to-dos, such as XSS, are the ones easier to forget.
Additionally, you need to really go out of your way to create a database insecurity whilst using CI, Kohana or Symfony.
Yes, it would be more secure. Though as yaron and tobias have pointed out, there shouldn't be any sql vulnerabilities in your app to begin with :p You'd also need at least two mysql connections per page, which could get quite resource intensive.
XSRF attacks are also ones you should look out for
+ As Tobias said, most major frameworks make it practically impossible to make security issues in your site
|Manofgames wrote: |
|+ As Tobias said, most major frameworks make it practically impossible to make security issues in your site |
I didn't read anything about a framework in mathiaus's post.
And even though it will decrease performance, it will make it just a bit harder for a hacker. Even if you use a framework, there could be vulnerabilities in the framework that allow sql injection.
|Abstraction layer, Rvec |
Even there you can make mistakes (even though it'll be harder to make them).
|rvec wrote: |
|I didn't read anything about a framework in mathiaus' post. |
I think everybody here agrees that he should at least use CI
|And even though it will decrease performance, it will make it just a bit harder for a hacker. Even if you use a framework, there could be vulnerabilities in the framework that allow sql injection. |
None of the aforementioned frameworks have any such vulnerabilities
|Even there you can make mistakes (even though it'll be harder to make them). |
You'd honestly need to absolutely fail at life to do so, though. Still, I can imagine some people from this specific forum (Php and MySQL) probably would somehow break it. Honestly, SQL injections are an incredibly small ricks for any non-dummies. If I remember correctly, preventing SQL injection is one of the first chapters of PHP and MySQL for Dummies. Additionally, the chance that you somehow code such a small amount of your site poorly is astronomical.
Regardless, none of these are problems for an experienced PHP-user like mathiaus.
Eep. I think Daniel15 is going to post here. Everybody, brace yourselves :O
My first post on FriHost in ages
Onto my thoughts on this.
Firstly, the MySQL extension in PHP only allows one query per mysql_query and mysqli_query call. Keeping this in mind, it means that if you have a query that reads data from a database, they will not be able to insert any data via injection into this query. SELECT queries have no way of inserting data into a table, and people can't just add an INSERT onto the end because only one query per function call is allowed. So, read-only accounts aren't really needed. Also, most SQL injection is via UNION, which is reading from other tables. In these situations, since it's all reading, the read only account will do nothing to stop this (unless you have a separate user per table, which I've mentioned below)
Escaping data is an incredibly simple thing to do, and no script these days should have any SQL Injection holes. Bind statements are another way of preventing SQL injection holes, data is passed separately to the query itself, and the data is automatically escaped. Some languages offer bind statements, but PHP doesn't support them (not in-built, anyways). However, several PHP frameworks do offer bind statements. For example, CodeIgniter does them like this:
|$this->db->query('SELECT * FROM blah WHERE something = ? AND somethingelse = ?', array('first thing', 'second thing')); |
As far as I can remember, the ADODB and ADODB Lite abstraction layers also support them.
As for your idea, it's a good idea in theory, but isn't really needed (due to what everyone has said, and my reasons above also). Each page would need a few MySQL connections per page, which is more resource intensive and not needed. As mentioned above with regards to UNION attacks, you could have a separate user per table, which would stop these for sure. It's definitely overkill, though, especially if your PHP code is secure (as it should be these days).
The problem with PHP is it has a relatively low learning curve, it's very easy to learn. While this is good for people that want to learn it properly, it's also bad because the barrier to entry is very low, meaning there's a lot of noobs (no offence to any PHP noobs reading this) that go on to sell commercial PHP scripts without properly securing them or knowing how to properly code ("properly" meaning securely, and also using best practices... Keeping application code separate from presentational code, using a proper design pattern, such as the MVC design pattern, etc.).
 MySQL API - http://dev.mysql.com/doc/refman/5.1/en/mysql-query.html
|Normally, the string must consist of a single SQL statement and you should not add a terminating semicolon (“;”) or \g to the statement. |
I don't have much experience with database security, but it seems like overkill. What can a hacker actually do once he gets into your databases? Be all, "I'm in ur database, updatin ur rows?"
|wylieconlon wrote: |
|I don't have much experience with database security, but it seems like overkill. What can a hacker actually do once he gets into your databases? Be all, "I'm in ur database, updatin ur rows?" |
If they can update your tables they can change passwords, insert malicious js etc. etc.