This is a very old article. It has been imported from older blogging software, and the formatting, images, etc may have been lost. Some links may be broken. Some of the information may no longer be correct. Opinions expressed in this article may no longer be held.
Firstly, three disclaimers: PHP is a great programming language, one of my favourites — this website is written in PHP; there are many great PHP programmers out there, some of whom probably never get things wrong; I probably get things wrong a lot of the time.
The majority of the database-backed Open Source PHP projects that I’ve used/examined make the same flaw again and again: they fail to make use of the security measures built into the database engine. A typical MySQL-backed PHP project will include some code like this near the top:
And why not? It’s the suggested code in the PHP manual. But let’s take a closer look and figure out what’s wrong…
Imagine that this code is supposed to be for a content management system. Certain users (let’s call them “Editors”) will use the content management system to make changes to the website’s content. Because they need to be able to make changes to the website, the MySQL user mysql_user has been GRANTed permission to edit the content tables in the database.
Most of the website’s users (let’s call them “Viewers”) will only ever be passively interacting with the website — reading content. Viewers use the same link to the database ($link) as the Editors, so as far as MySQL is concerned, they also have full access to the database, and are able to edit tables. It is only the developers PHP code that prevents them from doing so. Now, SQL injection is one of the first things developers learn about when learning to use databases. (If it’s not, then it should be!) But even the best developers occasionally slip up. If somewhere in the content management system code, there is a single SQL injection vulnerability, a malicious Visitor can obtain full read/write access to your database — they can add/edit/delete anything they want!
So what’s the solution? Firstly, set up your MySQL database with not one, but two different users. Give once full access to everything needed, and give the other one read-only access. Now, change your database connection code to something like this:
If a visitor does manage to find an SQL injection vulnerability, then at least all they can do it use it to read data, not change it.
If you are a developer writing PHP applications for distributions, please give your customers the option of connecting to the database with read-only users when appropriate.
If you are a web hosting provider offering PHP/*SQL hosting, please allow your customers to set up at least two different users in their databases.
