Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Databases _ MySQL root password and other configuration issues

Posted by: Christian J Jun 16 2010, 05:28 PM

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.

Posted by: Brian Chandler Jun 17 2010, 09:05 AM

The short answers is that I don't know: I use mysql on my hosting service (pair), and am saved having to administer the db server.

For each DB I create (using the pair web interface) I get new user names (actually three: full, read-write, and read-only) and passwords. I *guess* that you need the mysql _root_ password to do things like Create new database.

QUOTE
In phpinfo.php the "mysql.default_user" and "mysql.default_password" directives are listed. What are they used for?


I guess (again) that these are defaults php will use to connect to mysql. In a low/zero security system this would mean you don't need to suppy them to the mysql_connect() function. Perhaps?

At some stage I must have an offline system for testing my shop. I would be grateful to hear any feedback on how you get on.

Posted by: Christian J Jun 17 2010, 12:16 PM

QUOTE(Brian Chandler @ Jun 17 2010, 04:05 PM) *

I use mysql on my hosting service (pair), and am saved having to administer the db server.

Does that mean you must test DB-related PHP scripts online too, or are there tricks you can use to test without uploading them (like temporarily substituting the DB data with e.g. a PHP array)? If so maybe I don't really need an offline DB.

QUOTE
I guess (again) that these are defaults php will use to connect to mysql. In a low/zero security system this would mean you don't need to suppy them to the mysql_connect() function. Perhaps?

Could be. Can't remember if the root password was mandatory during the MySQL installation, if not the PHP defaults make sense.

QUOTE
At some stage I must have an offline system for testing my shop. I would be grateful to hear any feedback on how you get on.

So far it's just as terrible as I recalled it was last time I tried (and gave up). Can't even find a link to the MySQL manual on the site (Google found it though).

Posted by: pandy Jun 17 2010, 05:48 PM

I've never set up MySQL locally. It's been years since I even had Apache installed. Not much point anymore when it's almost as quick to play on the remote server with the advantage of being in the environment the stuff is supposed to work in all along.

Posted by: Christian J Jun 17 2010, 06:50 PM

QUOTE(pandy @ Jun 18 2010, 12:48 AM) *

Not much point anymore when it's almost as quick to play on the remote server with the advantage of being in the environment the stuff is supposed to work in all along.

That sounds pretty persuasive, but how about fixing errors on published pages? I rather do things like that offline, without any stress. When it comes to databases coding mistakes may even distort the data, so again it may be safer to work with a dummy DB offline.

Posted by: pandy Jun 17 2010, 07:49 PM

You set up a playground area on the server. smile.gif

Posted by: Christian J Jun 18 2010, 05:50 AM

QUOTE(pandy @ Jun 18 2010, 02:49 AM) *

You set up a playground area on the server. smile.gif

That's a thought, at least for PHP. Do you save a backup on your own computer? unsure.gif

Posted by: pandy Jun 18 2010, 11:16 AM

No, not unless its something very important. The server backups are seldom more than 2 days old, so I wouldn't lose very much. But that's me. If it's more important than my stuff is you could make daily backups or something.

Posted by: Frederiek Jun 19 2010, 07:08 AM

Or, simply use SQLite.

Posted by: Christian J Jun 19 2010, 02:40 PM

QUOTE(Frederiek @ Jun 19 2010, 02:08 PM) *

Or, simply use SQLite.

But then the web host must have it also?

Maybe XAMPP is another simple alternative for offline testing, but I didn't dare install it since I already had Apache and PHP.

Posted by: Christian J Jun 19 2010, 05:13 PM

QUOTE(Brian Chandler @ Jun 17 2010, 04:05 PM) *

I would be grateful to hear any feedback on how you get on.

Forgot to mention: using PHP I could create a DB, a DB table, and then populate the latter with some entries. So far all of it feels terribly involved compared with a nice flatfile DB, though.

Posted by: pandy Jun 19 2010, 10:18 PM

So it works. tongue.gif

Posted by: Brian Chandler Jun 20 2010, 12:23 AM

QUOTE(Christian J @ Jun 20 2010, 07:13 AM) *

QUOTE(Brian Chandler @ Jun 17 2010, 04:05 PM) *

I would be grateful to hear any feedback on how you get on.

Forgot to mention: using PHP I could create a DB, a DB table, and then populate the latter with some entries. So far all of it feels terribly involved compared with a nice flatfile DB, though.


I recommend using phpmyadmin (what a dreadful name!) for all "setup" operations. "Setup" basically means things you will only ever do once, so writing a program to do them is a bit inefficient.

Are you going to have a db system you administer every day? In which case it is definitely worth writing a program to arrange the updates conveniently. It's easy to present a colour-coded view of things like status codes. And each new program (for a new db table) gets easier and easier to write, because you copy the last one and change the bits that are different.

Incidentally, yes, currently I update everything directly on the server (which is not really the thing to do), except when I am really changing something, in which case I just give the file (program? web page? what should one call it?) a different name. But I think having a complete development system locally would be a better way to do it. I certainly keep copies of all files both on the server (one continent) and here (different continent), plus roughly monthy backup copies on a CD-R. Remember there is no chance of retrieving copies of programs from Google.

HTH. If you give us more concrete details of what you plan to do, perhaps more suggestions...

Posted by: geoffmerritt Jun 20 2010, 02:59 AM

I have Xampp for windows running on my laptop, which comes with php, apache, mysql, it is easy to setup and update.

localhost is the root of the server, setup of database can be done in phpmyadmin.

I like the fact I can create a complete working site on my pc and then export the *.sql file and then import the file to the web hosting service via phpmyadmin.

Using root in mysql as a user name should be avoided. It has been suggested to me that I should set up several users with different privileges and use the relevant user for the job. eg SELECT, the user should have read only privileges, etc. Maybe on my current project I will do this.


Posted by: Christian J Jun 20 2010, 07:28 AM

QUOTE(Brian Chandler @ Jun 20 2010, 07:23 AM) *

I recommend using phpmyadmin (what a dreadful name!) for all "setup" operations. "Setup" basically means things you will only ever do once, so writing a program to do them is a bit inefficient.

Otherwise setup with PHP was very simple (I just used http://www.w3schools.com/php/php_mysql_create.asp blush.gif ), but I guess phpmyadmin lets you do more things.

QUOTE
If you give us more concrete details of what you plan to do, perhaps more suggestions...

Just want to learn a little MySQL. Some kind of practice project might be useful, the hard thing is finding the motivation to use MySQL instead of a flatfile (which I'm more familiar with). While I'm sure MySQL is more efficient for large DBs, I'll hardly make a large one just for practice.

Posted by: Christian J Jun 20 2010, 07:40 AM

QUOTE(geoffmerritt @ Jun 20 2010, 09:59 AM) *

Using root in mysql as a user name should be avoided.

Even offline? On an online server, does the web host take care of things like this or do you have to do it yourself?

Posted by: pandy Jun 20 2010, 07:59 AM

You usually don't have root anything on a shared server.

Posted by: geoffmerritt Jun 20 2010, 08:27 AM

QUOTE(Christian J @ Jun 20 2010, 10:10 PM) *

QUOTE(geoffmerritt @ Jun 20 2010, 09:59 AM) *

Using root in mysql as a user name should be avoided.

Even offline? On an online server, does the web host take care of things like this or do you have to do it yourself?

Offline, guess it doesn't make a difference... If the web host uses cpanel you may need to create your own dbase, users and passwords.

I have my own hosting server, and replicate the databases identically on my laptop.... so i guess it a matter of course for me not to use root

Posted by: Frederiek Jun 20 2010, 12:26 PM

QUOTE(Christian J @ Jun 19 2010, 09:40 PM) *

QUOTE(Frederiek @ Jun 19 2010, 02:08 PM) *

Or, simply use SQLite.

But then the web host must have it also?

No, not as far as I know. Read up at http://devzone.zend.com/article/760-SQLite-Introduction

Posted by: Brian Chandler Jun 20 2010, 01:40 PM

QUOTE(Christian J @ Jun 20 2010, 09:28 PM) *

QUOTE(Brian Chandler @ Jun 20 2010, 07:23 AM) *

I recommend using phpmyadmin (what a dreadful name!) for all "setup" operations. "Setup" basically means things you will only ever do once, so writing a program to do them is a bit inefficient.

Otherwise setup with PHP was very simple (I just used http://www.w3schools.com/php/php_mysql_create.asp blush.gif ), but I guess phpmyadmin lets you do more things.


No, phpmyadmin cannot let you do more -- it makes it easier to do standard-ish things that you only do occasionally, in particular like creating tables. Actually I started with the mysql command line, but I honestly don't think you miss anything by skipping that.


QUOTE

QUOTE
If you give us more concrete details of what you plan to do, perhaps more suggestions...

Just want to learn a little MySQL. Some kind of practice project might be useful, the hard thing is finding the motivation to use MySQL instead of a flatfile (which I'm more familiar with). While I'm sure MySQL is more efficient for large DBs, I'll hardly make a large one just for practice.


The point of a DB is storing structured information: it's much easier to do this with DB columns than a "flat file" (assuming you just mean writing out stuff, reading it back in, and *parsing* it (which is the tedious bit).

Posted by: Brian Chandler Jun 20 2010, 01:45 PM

QUOTE(Frederiek @ Jun 21 2010, 02:26 AM) *

QUOTE(Christian J @ Jun 19 2010, 09:40 PM) *

QUOTE(Frederiek @ Jun 19 2010, 02:08 PM) *

Or, simply use SQLite.

But then the web host must have it also?

No, not as far as I know. Read up at http://devzone.zend.com/article/760-SQLite-Introduction


Um, well, unless I have misunderstood any of the words "No" and "Not", Christian is right -- of course the DB must be available on the web host if you want to run the same programs. But it is bundled with PHP5 (perhaps _not_ 4, if you're still on that), so the web host will almost automatically have it too.


Posted by: Christian J Jun 20 2010, 02:21 PM

I guess being bundled doesn't guarantee all web host actually wants to enable it (editing the PHP configuration is mentioned in the article) though I can't see why not.

Posted by: Christian J Jun 20 2010, 04:21 PM

SQLite enabled and tested! Indeed it's much simpler than MySQL, all that's needed with PHP5 was adding

CODE
extension=php_pdo.dll
extension=php_sqlite.dll

in the php.ini file.

Posted by: Christian J Jun 21 2010, 06:11 AM

http://devzone.zend.com/article/760-SQLite-Introduction#Heading8 says:

"If any query in SQLite uses user-specified input you should take extra care to validate that input, to prevent SQL injection. Unlike in MySQL, where this would only cause an embarrassing query error, in SQLite it would allow the attacker to execute a query on your server, with potentially disastrous consequences."

Could these potentially disastrous consequences make web hosts reluctant to allow SQLite?

Posted by: Brian Chandler Jun 21 2010, 06:46 AM

QUOTE(Christian J @ Jun 21 2010, 08:11 PM) *

http://devzone.zend.com/article/760-SQLite-Introduction#Heading8 says:

"If any query in SQLite uses user-specified input you should take extra care to validate that input, to prevent SQL injection. Unlike in MySQL, where this would only cause an embarrassing query error, in SQLite it would allow the attacker to execute a query on your server, with potentially disastrous consequences."

Could these potentially disastrous consequences make web hosts reluctant to allow SQLite?


Could these potentially disastrous consequences make web hosts reluctant to allow SQLite? --- I don't think so. I don't understand the basis for claiming that in mysql there would "only be a query error".

(You understand how sql injection works?) Simple program:

sql_call("SELECT * FROM thing WHERE fish ='" . $_GET['breed'] . "'");

The user is supposed to supply something like 'salmon', and this forms a single query to do the obvious thing.

But if the user supplies the string delineated by square brackets: [salmon'; DROP TABLE fish; 'x'='x]
... then this generates a perfectly valid sequence of SQL commands, where the middle one can do any mischief desired.

This "works" just as well on MySQL as anywhere else.

Posted by: Christian J Jun 21 2010, 08:58 AM

QUOTE(Brian Chandler @ Jun 21 2010, 01:46 PM) *

(You understand how sql injection works?)

Only vaguely...

QUOTE
But if the user supplies the string delineated by square brackets: [salmon'; DROP TABLE fish; 'x'='x]
... then this generates a perfectly valid sequence of SQL commands, where the middle one can do any mischief desired.

When I tried that I just got Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource, and the table is still there. I simplified the code a little:

CODE
#$result = mysql_query("SELECT * FROM Thing WHERE Fish ='Salmon'"); // works

$result = mysql_query("SELECT * FROM Thing WHERE Fish ='Salmon'; DROP TABLE Thing; 'x'='x'"); // doesn't work

#$result = mysql_query("DROP TABLE Thing"); // doesn't work

echo '<pre>';
while($row = mysql_fetch_array($result))
{
    print_r($row);
}
echo '</pre>';

Is something wrong with my code? Do I need the http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html#priv_drop (how can I tell, or set it)? Or has MySQL prevented this? Haven't tested with SQLite.

Posted by: Brian Chandler Jun 21 2010, 10:08 AM

QUOTE
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.

Posted by: Christian J Jun 21 2010, 12:11 PM

QUOTE(Brian Chandler @ Jun 21 2010, 05:08 PM) *

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 blush.gif ). In the example on http://php.net/manual/en/function.mysql-real-escape-string.php

CODE
"' OR ''='"

apparently turns into

CODE
'' OR ''=''

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


QUOTE
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?

Posted by: Brian Chandler Jun 21 2010, 10:33 PM

QUOTE(Christian J @ Jun 22 2010, 02:11 AM) *

QUOTE(Brian Chandler @ Jun 21 2010, 05:08 PM) *

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 blush.gif ). In the example on http://php.net/manual/en/function.mysql-real-escape-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.

QUOTE

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...


Posted by: Christian J Jun 22 2010, 11:04 AM

QUOTE(Brian Chandler @ Jun 22 2010, 05:33 AM) *

This is what gets concatenated into the query

So it's the single quotes here:

CODE
password='{$_POST['password']}'

that become concatenated with the injection string

CODE
' OR ''='

resulting in

CODE
'' OR ''=''

?

QUOTE

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.

Yes, and indeed http://www.php.net/manual/en/function.sqlite-query.php says that

"SQLite will execute multiple queries separated by semicolons"


Posted by: Brian Chandler Jun 22 2010, 01:16 PM

QUOTE(Christian J @ Jun 23 2010, 01:04 AM) *

QUOTE(Brian Chandler @ Jun 22 2010, 05:33 AM) *

This is what gets concatenated into the query

So it's the single quotes here:

CODE
password='{$_POST['password']}'

that become concatenated with the injection string

CODE
' OR ''='

resulting in

CODE
'' OR ''=''


?

CODE

This isn't "code", but I need to be in monospace...
I think you are confusing a *string* and a *representation of a string*. A string is a sequence of characters, any characters, including any sorts of quotes. But usually we show the string we mean (like a string representing a marine vertebrate) as "fish", or 'fish', using quotes to identify the string. But the quotes are not part of the string at all. It's easy to get confused  when the string *does* include quotes. As in the string  "'Bertie'", which is eight characters long, and consists of the name of said vertebrate enclosed in single quotes.

(So immediately I think you do not mean this: $password='{$_POST['password']}'  This is a syntax error, because the second ' closes a string. )

But anyway: I wrote

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

This sets the variable $_POST['password'] to a string of nine characters, which are the following (just the string, not any normal indicating quotation marks):

' OR ''='

These characters get concatenated with

SELECT * FROM thing WHERE password ='

(before) and (afterwards)

'

to form the invalid password check.


HTH

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)