MySQL root password and other configuration issues Options

[Christian J]

I'm finally going to try learning MySQL, and have installed a MySQL server on my computer (Windows with Apache and PHP). After some confusion I found that in order to succeed with PHP mysql_connect() I needed the username "root" and the MySQL root password created during the installation. Related questions:

- Is a root password necessary or useful on an offline testing server? Is it practical to make a new user account (with passwords?) in addition to root for each site I'm testing offline?

- Any other security precautions? For example there's a "skip-networking" directive, which stops MySQL from listening on a TCP/IP port.

- In phpinfo.php the "mysql.default_user" and "mysql.default_password" directives are listed. What are they used for? Apparently they're not the same as the MySQL root and password.

- What is http://www.php.net/manual/en/ini.core.php#ini.sql.safe-mode used for? When enabled mysql_connect() fails, apparently because it makes PHP ignore my root password. So when do you use safe mode --when passwords are not used (sounds contradictory)?

- Anything else to think of, so that my offline configurations won't differ too much from what online webhosts look like? I don't have any web host offering MySQL, so I can't check.

[Brian Chandler]

Is something wrong with my code? Do I need the DROP privilege (how can I tell, or set it)? Or has MySQL prevented this? Haven't tested with SQLite.

I'm sorry, I was only sketching -- you need to work out the details (the script kiddies already have).

The basic error is assuming that a user input will be the sort of string it is meant to be. So typically you use mysql_real_escape_string() to ensure that the string will not leak outside the surrounding quotes. That's why (I am told by people whose names I have fogotten) you can get into "signup" boxes you don't really belong to by entering in the password box

['; 'x'='x]

which converts an SQL test for a password match into an expression with the value TRUE. Well, roughly.

Anyway, I don't understand the details of SQL "privileges"; pair gives me three logins for each database: full access, read-write (can't create or drop tables), and read-only. Don't think that's the problem anyway: your error message says that $result is not a valid resource -- when the SQL command doesn't return rows (as DROP, or CREATE, etc don't), then I don't suppose you can expect to get a row with mysql_fetch_array().

More basically, one thing you can be *absolutely* certain of: MySQL has not "detected an unauthorised action" and stepped in to make sure all children are safe.

[Christian J]

typically you use mysql_real_escape_string() to ensure that the string will not leak outside the surrounding quotes.

Can't quite understand how that leaking takes place (sorry about going more and more OT in my own thread ). In the example on http://php.net/manual/en/function.mysql-re...cape-string.php

CODE

"' OR ''='"

apparently turns into

CODE

'' OR ''=''

as if PHP/MySQL don't see the differentce between single and double quotes?

Don't think that's the problem anyway: your error message says that $result is not a valid resource -- when the SQL command doesn't return rows (as DROP, or CREATE, etc don't), then I don't suppose you can expect to get a row with mysql_fetch_array().

But shouldn't the table be dropped before the warning message?

Tested some more, and this does drop it:

CODE

mysql_query("DROP TABLE Thing");

(and also as a variable: $result=mysql_query("DROP TABLE Thing"); --my previous post was wrong), but this does not drop:

CODE

$result=mysql_query("SELECT * FROM Thing; DROP TABLE Thing");

--could it be that you can't select and drop in the same query?

[Brian Chandler]

typically you use mysql_real_escape_string() to ensure that the string will not leak outside the surrounding quotes.

Can't quite understand how that leaking takes place (sorry about going more and more OT in my own thread ). In the example on http://php.net/manual/en/function.mysql-re...cape-string.php

CODE

"' OR ''='"

apparently turns into

CODE

'' OR ''=''

as if PHP/MySQL don't see the differentce between single and double quotes?

No, nothing "turns into" anything else. In the example given the "trick" password string passed is shown by:

CODE

$_POST['password'] = "' OR ''='";

In other words the *string* passed is the *contents* of the double quotes. Viz:

CODE

' OR ''='

This is what gets concatenated into the query, resulting in "password matches blank OR blank=blank", which is always true. (Perhaps this is what unknown sources have said can be typed into a login box.)

What I meant by "leaking" is: the programmer *intends* that the user input supplied is just a string to be compared with some DB value. But by not sanitizing any included quotes, this string can "leak" out to form a larger expression, with a different sort of value ("always true"). Hope this is clear now.

CODE

$result=mysql_query("SELECT * FROM Thing; DROP TABLE Thing");

--could it be that you can't select and drop in the same query?

Seems I was (partly) wrong: http://www.php.net/manual/en/function.mysql-query.php
Quote: "multiple queries are not supported" -- which presumably means no semicolons. So it is not simple to execute arbitrary commands such as DROP as I thought, and perhaps this is what the SQLite people meant.

In any case, it's good style to program defensively...