[UPHPU] MySQL questions?

Mac Newbold mac at macnewbold.com
Fri Apr 23 08:57:31 MDT 2004


Today at 6:46am, David Smith said:

>Also, a good PHP/MySQL design involves multiple users, one for read-only,
>one for writing, etc. This is for security purposes. I'd like to see a
>presentation on that.

I don't know that I agree with that statement, at least from a practical
stand point.

In my experience, the biggest security concern regarding MySQL access
through PHP is the user input that gets processed and used as part of a
query. I can't think of a good reason why you'd ever let the user choose
what type of SQL query to do (like SELECT vs INSERT), and when you've
decided that, separate users doesn't seem to buy anything.

Any time your query is a SELECT, you could use the read only user, but
that still means they could read anything, and because it is a SELECT,
they couldn't have changed anything anyway.

Any time the query is an INSERT, REPLACE, etc., you'd have to use the
write user, which means you can write anything.

The PHP mysql functions do something really nice for security, too: they
prevent sending multiple queries in a single database call. (They also
magically add slashes, but that's a different story.) This is the real
place you would have had to worry. For example, say I told your web site
that my name was 'Mac"; DELETE from users; select "' . If you didn't
refuse to allow quotes in that string, then when inserted in a query, you
may get this query:

UPDATE users set name="$name";

Doing something like this:

UPDATE users set name="Mac"; DELETE from users; select "";

Note that it is still valid sql, and wouldn't fail. But PHP has some magic
"addslashes" features that are designed for use with databases. And they
don't allow multiple queries in the same call. So nothing after the first
semi-colon would ever get seen by the database, if it even made it that
far.

Mac

--
Mac Newbold		MNE - Mac Newbold Enterprises, LLC
mac at macnewbold.com	http://www.macnewbold.com/



More information about the UPHPU mailing list