Mark's Technomusings

Secure password storage w/ PHP & MySQL

If the salt is random, how do we reliably generate the same salt value (For the same user) next time they log in?

The answer is to simply store the generated salt with the user pass.
For example I use PHP and MySQL and so a simple user information table only needs 3 fields: `username`, `pass`, `salt`

When someone tries to log in then you can check if the details are right by using the following:
$user = mysql_real_escape_string("USERNAME");
$pass = mysql_real_escape_string("PASSWORD");
$result = mysql_query("SELECT COUNT(`username`) FROM `table` WHERE `username` = '$user' AND `pass` = MD5(CONCAT('$pass', `salt` ))");
if(mysql_result($result,0) == 1)
{
echo "Logged in correctly.";
}
else
{
echo "Sorry, no dice.";
}

Using MySQL from Microsoft Access

Using MySQL from Microsoft Access describes in great detail (and good screenshots) how to connect to a MySQL database from MS Access. This allows you to add/delete/insert data using Access as a front end. All of the changes actually take place on the MySQL server.

---

Before you do this, you will need to make sure that MS Access has permissions to connect to MySQL. Set up those permissions this way:

[root@server root]# mysql -u root mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 519002 to server version: 4.0.24-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> INSERT INTO user VALUES ('IP address or host name','username',password('some_password_in_cleartext'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0);
Query OK, 1 row affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

There is a slight change that you might need to make a change to the db:

INSERT INTO db VALUES (IP address or host name','database name','username','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y');

Mysql binary log maintenance

I've been noticing that my /usr partition has been slowly getting more and more full. I finally figured out why. I had enabled MySQL to create binary logs, and the log file was growing quite large:

[root@ns data]# ls -sh *bin*
4.0K ns-bin.001  920K ns-bin.004   12M ns-bin.007
4.0K ns-bin.002  4.0K ns-bin.005   69M ns-bin.008
644K ns-bin.003  4.0K ns-bin.006  4.0K ns-bin.index
[root@ns data]# mysqladmin flush-logs
[root@ns data]# ls -sh *bin*
4.0K ns-bin.001  920K ns-bin.004   12M ns-bin.007  4.0K ns-bin.index
4.0K ns-bin.002  4.0K ns-bin.005   69M ns-bin.008
644K ns-bin.003  4.0K ns-bin.006  4.0K ns-bin.009

As you can see, right now, the current logfile is ns-bin.008 and has a size of 69MB! After running mysqladmin flush-logs, there is now a new 4.0kb ns-bin.009 file....and the ns-bin.008 file is still there...but I can cancel it and free up 69MB of space.