[UPHPU] MySQL questions?

Mac Newbold mac at macnewbold.com
Fri Apr 23 17:57:33 MDT 2004


Today at 4:05pm, Steve Dibb 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.
>
>A practical approach might be to have a user that has readonly access to
>create backups of the database on a cron job.

Sure, that's a fine reason, but you probably wouldn't be doing that from
PHP.

>Another might be to have your code scripted so that you have a user that
>can only do selects as well, if the main thrust of your page is simply
>delivering content instead of updating it on the fly.

Yes, a complete read-only site could take advantage of it quite nicely.
Although, it would be pretty hard to make a read-only site that was at
risk of SQL injection attacks anyway.

>Then you can have another mysql user connection for your admin backend,
>or use it with phpMyAdmin, or just running mysql on the console, and
>that one has full privileges.

You could definitely do that, and it wouldn't be a bad idea. If they can't
get a chance to get user-supplied info to a place where it might be used
in a database write query, then the damage they can do is pretty small, at
least in terms of overwriting your database.

The reasons I think that it isn't always practical to have separate user
accounts for accessing the database with higher/lower priviledges:

1) I would venture a guess that most sites either have a significant
amount of updating, or no updating at all. If you're doing a lot of
automated updating from the web site, it isn't going to get you a whole
lot of protection to use a different db connection for all your selects.
They're not really a high risk for causing problems anyway.

2) In most cases, if they can get to someplace where they can compromise
your db user name/password, they can most likely compromise your
priviledged username/password just as easily as they could your read-only
one.

3) Dealing with two connections can make it easier to slip up and use the
wrong one, like using the admin one for a read, which would still work.
And when it is easy to slip up, but you think you're protected, it gives a
false sense of security that makes us think we don't need to worry.

A lot of security issues and possible solutions are discussed, like these,
in a context where the threat you're trying to discuss has not been
clearly identified. If the threat is SQL injection through your web site,
the most important protections are already in place (mysql_query() allows
only one query per call, and magic_quotes_gpc defaults to on). So to open
yourself up to problems you actually have to try (turn magic quotes off,
or stripslashes before you do the db call), in addition to being sloppy
(not checking user-supplied parameters before inserting them in a db
query).

Maybe I'm not catching which threat you're concerned about, or I might be
missing the attack vector that you're thinking of.

Mac

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



More information about the UPHPU mailing list