MySQL II : Fedora MySQL Installation

Install MySQL 5.0 on Fedora 4

1. You’ll need to be starting from a machine that has MySQL 4.x clients and development libraries installed. For this class we’re using Fedora 4 linux boxes, so we’re using RPM installations and defaults.

Earlier versions of MySQL appear in the RPM libraries with the spelling (lowercase) “mysql” instead of the later “MySQL” (uppercase) spelling; try the lowercase versions first. Run this query:

rpm -q mysql

rpm -q mysql-server

If you get a match, and the version is 4.0 or later, skip the next two. If you don’t get a match, run these:

rpm -q MySQL-client

rpm -q MySQL-server

If you still don’t get a match, get and install MySQL 4.0 or later via RPM or installation CD.

You will need a version that includes the file libmysqlclient.so.14, which in my experience means 4.0 or later. Your results may vary. Just be sure you have this file.

Once you do have the mysql (or MySQL) packages installed, check for these. They’ll make life simpler later on.

rpm -q MySQL-python

rpm -q MySQL-devel

rpm -q MySQL-shared

 

2. Prepare for the upgrade.

Go to the /usr/lib directory. You’ll have a mysql directory. Copy it. The name you choose is irrelevant; backing up this directory is critical.

Also, make a copy of /etc/my.cnf. RPM package removal did not, in my case, delete this file, but you will need it for a template later, so back it up regardless.

If your installed version of MySQL 4.x is like mine, you’ll see that the server and data previously installed in /var/lib/ and /var/lib/mysql, respectively. While you can leave your datadir defined as /var/lib/mysql (or change it to whatever you want), you must COMMENT OUT the basedir line:

basedir-/var/lib

to this:

#basedir=/var/lib

If you don’t, your installation flatly will not work. MySQL 5 installs its binaries in /usr/lib/mysql (the basedir).

 

3. Uninstall MySQL 4.x:

rpm -e mysql

rpm -e MySQL-client

rpm -e MySQL-server

 

4. Install MySQL 5.0 and its development headers and libraries.

Now get MySQL proper. The latest, by far greatest release is 5.0/5.1, as of this writing  (11/30/2005).

  • static vs. dynamic libraries
  • source vs. binary distributions
  • .tgz vs. .rpm
  • processors

    A. Go to http://dev.mysql.com/downloads/mysql/5.0.html (5.0 is the current stable release) and get the MySQL 5.0 client and server RPMs from the Linux x86 generic RPM downloads section.

    Also get the mysql-devel package (it’s labeled “Libraries and header files“).

    B. Install the RPMs.

    rpm -i <rpmfile>  #substitute the rpm files’ names

    RPMs will install MySQL 5 binaries in /usr/lib/mysql/ by default. in /var/lib/mysql, and will put startup scripts in the /etc/rc.d directory. (Who owns /var/lib/mysql?)

    You are going to get an error message that you are missing a shared library: libmysqlclient.so.14.

C. Remember the /usr/lib/mysql directory that you copied? Restore the files it contains (I’d copy, so I could keep this resource directory) back to the new /usr/lib/mysql directory. You’ve physically restored libmysqlclient.so.14; now you have to re-register it.

Open the file /etc/ld.so.conf . Add the line:

/usr/lib/mysql/libmysqlclient.so.14

Save and close the file.

Run the command:

ldconfig

This loads the library back into the active system configuration.

(How do you find solutions like this? I went straight to the MySQL forum:

User Mike Rappe wrote:

I installed Fedora Core 4 which installed MSQL 4.1.11. I needed the features in ver 5 so I installed it from RPM MySQL-server-5.0.15-0.i386.rpm but I got errors about libmysqlclient.so.14 being needed so I installed MySQL-shared-standard-4.1.15-0.rhel3 and was able to install MySQL-server-5.0.15-0.i386.rpm without any errors. This was the command I used:

rpm -Uhvv MySQL-server-5.0.15-0.i386.rpm

Lenz Grimmer <lenz at (@) mysql dot (.) com> provided the solution:

It seems like the original Red Hat MySQL RPM left a /etc/my.cnf file around – it contains a line that changes “basedir” to /var/lib, which causes the startup script to look for the binaries at the wrong location. Please either comment out that basedir variable or remove /etc/my.cnf completely, if you have not further customized it yet. Better start with a fresh one from scratch. Our RPMs don’t ship with a /etc/my.cnf file by default, but they honour an existing one.

This did the trick for me!)

3. Set the root user’s password for MySQL:

msqladmin -u root password ‘private’

4. Create a user configuration file for root:

vi ~/.my.cnf

Containing this texti:

[client]
password=’private’

 

Return to Intermediate MySQL Home Page

 

Go to http://dev.mysql.com/downloads/mysql/ get MySQL server and client. Copy or move both to /usr/local/ and unpack them:

tar -zxvf mysql-standard-<version>.tar.gz
tar -zxvf mysql-client-<version>.tar.gz

You’ll end up with a directories named like mysql-standard-<version>.tar.gz_FILES. I prefer to cd into these and then move the mysql-standard-<version> directories up outside it, and delete the _FILES directories.

Create symbolic links to the new directories:

ln –s /usr/local/mysql-<version> mysql

Create a mysql user and group:

useradd mysql
<enter a password, twice>

Move to the MySQL directory:

cd mysql

Configure and install:

./configure –prefix=/usr/local/mysql

Note that if you install MySQL 5.0 or later this way, you will not run the following two commands.

make
make install

Install the default databases:

cd /usr/local/mysql/scripts
./mysql_install_db
cd ..

Change the permissions on the new files, which allows MySQL to run as the new mysql user that you created earlier:

chown –R root /usr/local/mysql/
chown –R mysql /usr/local/mysql/data
chgrp –R mysql /usr/local/mysql/

2. If you will be adding Python, also get the mysql-devel package (it’s labeled “Libraries and header files”).

 

MySQL II : 4.x Installation

Installing MySQL 4.x and Earlier

1. Before starting, install the MySQL servers, clients and development libraries. Run these queries, and if necessary, install the related packages:

rpm -q mysql
rpm -q mysql-server
rpm -q mysql-devel
rpm -q MySQL-python
rpm -q perl-DBD-MySQL
rpm -q php-mysql

-and any other indicated dependencies as necessary.

The easiest way to install all of these is from the Fedora Core 4 CDs or DVD. If you go to the Internet and get “aftermarket” RPMs, be SURE the version numbers match exactly!

When you install from the Fedora CDs or RPMs, the mysql user and group will be created automatically, and directory permissions will be pre-set for you.

2. Set the root user’s password for MySQL:

msqladmin -u root password ‘private’

I will be using, as a default, the password “private” for all root connections.

4. Create a user configuration file for root:

vi ~/.my.cnf

Then in vi:

[client]
password=’private’

 

Return to Intermediate MySQL Home Page

 

Go to http://dev.mysql.com/downloads/mysql/ get MySQL server and client. Copy or move both to /usr/local/ and unpack them:

tar -zxvf mysql-standard-<version>.tar.gz
tar -zxvf mysql-client-<version>.tar.gz

You’ll end up with a directories named like mysql-standard-<version>.tar.gz_FILES. I prefer to cd into these and then move the mysql-standard-<version> directories up outside it, and delete the _FILES directories.

Create symbolic links to the new directories:

ln –s /usr/local/mysql-<version> mysql

Create a mysql user and group:

useradd mysql
<enter a password, twice>

Move to the MySQL directory:

cd mysql

Configure and install:

./configure –prefix=/usr/local/mysql

Note that if you install MySQL 5.0 or later this way, you will not run the following two commands.

make
make install

Install the default databases:

cd /usr/local/mysql/scripts
./mysql_install_db
cd ..

Change the permissions on the new files, which allows MySQL to run as the new mysql user that you created earlier:

chown –R root /usr/local/mysql/
chown –R mysql /usr/local/mysql/data
chgrp –R mysql /usr/local/mysql/

2. If you will be adding Python, also get the mysql-devel package (it’s labeled “Libraries and header files”).

MySQL II : Procedures, Views & Triggers

MySQL 5:
Using MySQL With Python
Using Stored Procedures, Views and Triggers

 

Go to this page:

http://www.kitebird.com/articles/pydbapi.html

Perform the tests at the top of the page to confirm your installation.

Then work your way through writing and running the animal.py script.

 

Sign Up With LinuxMagazine.com .

For the first unit, go the the article MySQL 5 Stored Procedures, Views, and Triggers.

For the second unit, go to the article MySQL 5 Stored Functions and Cursors.

 

You may also need:

users.sql

board.sql

htaccess

htpasswd

xindex.php

Listing1.sql

Listing2.sql

Listing3.py

Listing4.sql

Listing5.py

Listing6.sql

Listing7.sql

Listing8.sql

Listing9.sql

Listing 10.sql

 

MySQL II : Python

Setting Up MySQL With Python

1. Go to http://www.sourcekeg.co.uk/www.python.org/2.4.2/rpms.html and get python24-2.4.2-2.i386.rpm.

Install this by issuing the command:

rpm -i python24-2.4.2-2.i386.rpm

(Historical note: you can get a tarball and install it like this:

Go to http://python.org/2.4.2/ and get Python 2.4.2 (http://python.org/ftp/python/2.4.2/Python-2.4.2.tgz or http://python.org/ftp/python/2.4.2/Python-2.4.2.tar.bz2 – the bz2 is smaller). Copy or move it to /usr/local/ and unpack with:

tar -zxvf Python-2.4.2.tgz
or
bzcat Python-2.4.2.tar.bz2 | tar -x
f –

Then:

./configure
make
make install

)

2. Go to http://rpm.pbone.net/index.php3/stat/4/idpl/1953238/com/python-devel-2.4.1-2.i386.rpm.html to get python-devel-2.4.1-2.i386.rpm. Install this:

rpm -i python-devel-2.4.1-2.i386.rpm

 

This is the Python interface to MySQL.

1.  Go to http://sourceforge.net/projects/mysql-python/ and download (http://prdownloads.sourceforge.net/mysql-python/MySQL-python-1.2.0.tar.gz?download).

2. Get the README (http://sourceforge.net/docman/display_doc.php?docid=26237&group_id=22307) and review it.

3. Generally your installation will be like this:

tar xfz MySQL-python-1.1.8.tar.gz
cd MySQL-python-1.1.8
export mysqlversion=”5.0.16″
export mysqlclient=”mysqlclient_r”
export mysqloptlibs=”ssl crypto”
python setup.py build
su # or use sudo
python setup.py install

 

If you’re going to follow the demonstration on the Python Development page, you’ll need the following packages as well.

 

fpconst is a development library necessary for the SOAPy component. Go to http://research.warnes.net/downloads/ and get the latest .gz version of fpconst.

Extract the tarball and go into the resulting directory from the command line. Command:

python setup.py install

You’re done here.

 

pyXML is another package of libraries for processing XML using Python. Go to http://pyxml.sourceforge.net and download the latest .tar.gz file.

Extract the tarball and go into the resulting directory from the command line. Command:

python setup.py build

(Yes, there’s an extra step to compile this package.) Now command:

python setup.py install

Finito. (This isn’t that hard, is it?)

 

Go to http://sourceforge.net/project/showfiles.php?group_id=26590 and get SOAPy (download SOAP.py-<version>.tar.gz).

Extract the tarball and go into the resulting directory from the command line. Command:

python setup.py install

That’s it. You’ve gotta love python.

 

See “Writing MySQL Scripts with Python DB-API” by Paul DuBois at http://www.kitebird.com/articles/pydbapi.html. Run this test:

python
>>> import sys
>>> sys.version
#You should get results similar to:
‘1.5.2 (#1, Aug 25 2000, 09:33:37) [GCC 2.96 20000731 (experimental)]’
>>> import MySQLdb

This should return you to the prompt:

>>>

If instead you get:

>>> import MySQLdb
Traceback (most recent call last):
File “<stdin>”, line 1, in ?
ImportError: No module named MySQLdb

then it’s time to go to the top of the list and review what you’ve done.

MySQL II : PHP

Using MySQL With PHP

If necessary, get the PHP API connector at http://dev.mysql.com/downloads/.

 

PHP Basics

Open and close scripts like this:

<?php
$var = “Hello world!”;
print $var;
?>

 

PHP variables (like many Perl and Bash variables) begin with $ .

Use a single = sign to assign a value:

$interpreted_variables = “variables that get interpreted”;
$uninterpreted_variables = “variables that DON’T get interpreted”;

$var1 = “strings and $interpreted_variables”;
$var2 = ‘strings and $uninterpreted_variables’;

Use a double == sign to read a value:

if ($var1==”strings and variables that get interpreted”) {
//do something;
}
else
{
//do something else;
}

Use a print statement to print out the variable’s value:

print $var1;
//This gets you: strings and variables that get interpreted
print $var2;
//This gets you: strings and $uninterpreted_variables

 

Constants look like this:

DEFINE(DB_USER, “username”);
DEFINE(DB_PASSWORD, “password”);
DEFINE(DB_HOST, “hostname”);
//this could be “localhost”
DEFINE(DB_NAME, “database_name”);
//this could be “albums”

 

Concatenate string or variable values with “dot” syntax: “string1”.”string2″ or value1.value2

 

Place any string that might contain characters that need to be escaped (with the escape character, \) into an addslashes function. This is especially true of a string that will be returned when a variable is interpreted:

$safe_string = addslashes($HTTP_POST_VARS[‘text_box_name’]);

 

Use the mysql_connect() function:

$db_connection=mysql_connect(host, user, password)

 

When you’re done, be sure to close the connection:

mysql_close();

 

Use the mysql_select_db() function:

mysql_select_db(‘albums’);

or:

mysql_select_db(DB_NAME);

or:

use an include file as described below.

 

Use the mysql_query() function:

$query = “SELECT * FROM albums”;
$query_result=mysql_query($query);

BE CLEAR! DO NOT USE A SEMICOLON ( ; ) INSIDE THE QUERY IN PHP!

Be sure to clear this variable when you’re done:

mysql_free_result($query_result);

 

Use the mysql_fetch_array(array, parameter) function:

$query = “SELECT * FROM albums”;
$query_result=mysql_query($query);
while($row=mysql_fetch_array($query_result) {
//Do things with $row
}

One great use for this is creating values that will be placed in a pick list (a drop-down menu):

echo ‘<form action=”album_info.php” method=”get”>’;
echo ‘Choose an album:
<select name=”album_list”>’;

$query = “SELECT album_id, album_name FROM albums”;
$query_result=mysql_query($query);
while($row=mysql_fetch_array($query_result, MYSQL_NUM) {
echo “<option value=\”$row[0]\”>$row[1]</option>”;
}

echo ‘</select>
<input type=”submit” name=”submit” value=”Show This Album”>
</form>’;

The mysql_fetch_array() function does an interesting thing: by default, it returns every row twice, once with an associative key, and once with a numeric (index) key. You can specify which you want by using the optional second parameter: MYSQL_NUM, MYSQL_ASSOC, or MYSQL_BOTH (which really just does the same thing as using no parameter):

Using MYSQL_NUM

Notice that in the example above, the two values returned in each row are accessed by column index, starting with 0. The first column (album_id) is $row[0], and the second column (album_name) is $row[1]. This results from adding the MYSQL_NUM parameter to the mysql_fetch_array() function

Using MYSQL_ASSOC

When you specify MYSQL_ASSOC, the two values returned in each row are accessed by the index column value. If the first column value (album_id) is “Beatles 1”, and the second column (album_name) is “Meet the Beatles,” you would refer to this row as $row[‘Beatles 1’].

Using MYSQL_BOTH

This takes you right back to returning every row twice. Perhaps this is useful in some situations(?).

 

Get the value from the $HTTP_POST_VARS() array:

$query=”INSERT INTO albums VALUES (NULL, ‘{HTTP_POST_VARS[‘album_title’]'”;

This requires a text box, of course, named “album_titles.”

Note that you can use the “superglobal arrays” $_POST and $_GET, which function the same way but are global in scope. (See this page for a discussion of scope.)

Then execute the query:

$query_result=mysql_query($query);

If you actually get a successful query result, $query_result() will return a positive value. So you can use it in a conditional:

if ($query_result) {
echo “It worked.”;
}
else
{
echo “It didn’t work.”;
}

You could combine these last two code blocks:

if (mysql_query($query)) {
echo “It worked.”;
}
else
{
echo “It didn’t work.”;
}

 

Use standard HTML syntax:

<html>
<head></head>
<body>

<?php
//php code here
?>

<form action=”albums.php” method=”post”>
<!– Notice that the comment characters have changed. –>
<!– Also: be clear about when you use post and when you use get!–>
Name: <input type=”text” name=”name” maxlength=10>
Password: <input type=”password” name=”password” maxlength=8>
<input type=”submit” name=”submit” value=”Log In”>
</form>

<?php
//concluding code here
?>

 

Use a conditional like this:

if (isset($HTTP_POST_VARS[‘submit’])) {
//Do things
}
else
{
//Do other things
}

This will check the post variables array for a value named “submit.” In order for this variable to be set, your submit button must be named “submit.” You can change the button’s name, but you must also change this value if you do.

 

Use a conditional here as well:

if (strlen($HTTP_POST_VARS[‘user_name’]) > 0) {
//Do things: note the strlen() function, which returns
//the string’s length as an integer
}
else
{
echo “Hey, you forget to enter your user name.”;
}

 

PHP has a function similar to the LAST_INSERT_ID() function in MySQL, which is the mysql_insert_id() function:

$last_id=mysql_insert_id();

You’re likely to need this when you’re using the primary key from one table as a foreign key in another, for instance an albums table with three columns, album_id, album_title, and album_genre:

$query=’INSERT INTO genre VALUES (NULL, “60s Rock”)’;
$last_id=mysql_insert_id();
$query_result=mysql_query($query);
$query2=’INSERT INTO albums VALUES (NULL, “Meet the Beatles”,’.$last_id.’)’;

 

Output the error number if something failed:

$db_connection=mysql_connect(host_name, user_name, password) or die(mysql_errno());
//die will stop the script at this point if the connection fails, and the error number will print

Output the error description if something failed:

$db_connection=mysql_connect(host_name, user_name, password) or die(mysql_error());
//die will stop the script at this point if the connection fails, and an error message will print

You can also suppress error messages:

$query_result=@mysql_query($query);

Because there is no “die” statement, execution will continue, even if an error occurs.

 

Get the mysql script to create the database: board.sql. Run this script in the mysql monitor.

Get this PHP script: index.php. You must copy it to your web root (/var/www/html). Is your web server running? Is your MySQL database running?

Now, consider this code:

<html><head><title>My PHP Page</title></head>
<body>

<form action=”index.php” method=”post”>
Name: <input type=”text” name=”myname” maxlength=20>
Comment: <input type=”text” name=”message” maxlength=255>
<input type=”submit” name=”submit” value=”Submit Comments”>
</form>

<?php
// Define login info.
$host = ‘localhost’; // DATABASE SERVER;
$database = ‘board’; // DATABASE NAME;
$user = ‘root’; // USER TO CONNECT TO DATABASE;
$password = ‘private’; // USER PASSWORD;

// Establish a link to MySQL.
$db_link = mysql_connect(“$host”,”$user”,”$password”);
if ($db_link==FALSE) {
print “Database connection failed”;
exit;
}

// Select the database
mysql_select_db(“$database”);

// Set up a query, and perform an insert
$query=”INSERT INTO board.guestbook VALUES (‘{$_POST[‘myname’]}’,'{$_POST[‘message’]}’,NOW())”;
$result=mysql_query($query);

// Now perform a select
$query=”SELECT * FROM guestbook”;
$result=mysql_query($query);

// Loop through the result set.
$num = mysql_num_rows($result);
$cur = 1;

while ($row = mysql_fetch_array($result)) {
print $row[‘myname’].’ – ‘.$row[‘message’].’ – ‘.$row[‘date’].'<br>’;
}

// Free up resources
mysql_free_result($result);
mysql_close();
?>
</body></html>

This is a good starting point, but it has one major weakness: if the page fails to load properly for some reason, the viewer will likely be able to view this source code and get your user name, password, hostname and database name.

You must never allow this to happen.

Place your database connection information outside the root of your web site, preferably one or more folders up from public_html. Remember: PHP runs in the context of your local server, not in the web server’s context (limited to the web root and below).

You can place an included connection script in a completely separate file, typically using the .inc file extension, to indicate an include file. Name this file something like for instance, db_connection.inc:

<?php
$server = ‘rhonda’; // DATABASE SERVER;
$database = ‘med_records’; // DATABASE NAME;
$user = ‘joe_user’; // USER TO CONNECT TO DATABASE;
$password = ‘PrEc1s10n’; // USER PASSWORD;
?>

Now you can use this connection file as an include from each web page html file:

include(‘../db_connection.inc);

or:

require_once(‘../db_connection.inc);

 

1. Always conceal your database connection information. See Includes, above.

2. Retrieve submitted values using the $HTTP_POST_VARS() and $HTTP_GET_VARS() arrays, or the “superglobal arrays” $_POST and $_GET, which function the same way but are global in scope. (See this page for a discussion of scope.) See Inserting Values, above.

These arrays specifically decode hexadecimal encoded characters for you, saving you the trouble of dealing with un-encoding with the urldecode() and similar functions (see this discussion for some examples of how allowing encoded characters can make you vulnerable to SQL injection, among other things).

3. You should also look at the preg_replace() function, and particularly its use in preventing cross-site scripting here, by removing http:// references to other web sites.

4. To correctly (safely) handle special characters, use one of these functions:

addslashes()

or:

mysql_escape_string() //as of PHP 4.0.3

$query=”INSERT INTO albums VALUES (NULL, ‘ “. mysql_escape_string($HTTP_POST_VARS[‘album_name’]) ” ‘ “;

or see if your web server PHP configuration has Magic Quotes enabled (Magic Quotes is disabled by default).

Elizabeth Fulghum’s PHP Series:

Learning PHP – http://www.developer.com/lang/php/article.php/3288671

Building Database-Driven Applications with PHP and MySQL – http://www.developer.com/lang/php/article.php/2203971

 

MySQL II : Elementary Security

Resources

See “Securing Your MySQL Installation” by Paul DuBois at http://www.kitebird.com/articles/ins-sec.html.

 

From now on, when you log in, command:

mysqladmin -u root -p

MySQL will then prompt you for root’s password.

You could also log in like this:

mysqladmin -u root -psomething_more_secure

Note that there is no space between the “-p” and the actual password. Also note that this is a terrible idea, because your password will be clearly visible.

More specifically, users can and should use .my.cnf files in their home directories to store their passwords if they’re using good, complex passwords. However, see Secure All Option Files below.

 

Command:

useradd mysqluser #assuming you want to name this user mysqluser

Now make sure that mysqld is running under that user’s permissions by configuring the global configuration file, /etc/my.cnf:

[mysqld]
user=mysqluser

 

RPM installation will put MySQL in /var/lib/mysql, and add startup scripts to /etc/rc.d/ .

Installation from a .tar.gz package can be done to /usr/local, /usr/bin, or the location of your choice.

Know the location of the mysql executables on your system.

Assume for this example that the executables are in /usr/local/mysql/ , and the data directory is /usr/local/mysql/data/ .

Set the OS permissions so that the mysql user, and that user’s group, own the appropriate directories. You may have to modify the path, depending on your installation:

chown -R root /usr/local/mysql/. #This changes user ownership
#of the mysql directory, and everything inside it.

chown -R mysql /usr/local/mysql/data #This changes user ownership
#of the data directory, and everything inside it.

chgrp -R mysql /usr/local/mysql/. #This changes group ownership
#of the mysql directory and everything inside it (-R).

Now remove the permissions of other users on these directories:

chmod -R 770 /usr/local/mysql/

This denies all access to other users, even browse permissions.

You’re stuck running mysqld as the System user, which is roughly the equivalent of the Unix root user. While nobody can log in as System (which is good), all users can request resources through System (which is not so good). Configure all other users’ access tightly.

 

There’s a file named /usr/local/mysql/mysql.sock (or /var/lib/mysql/mysql.sock) that has a special function: it allows a connection (socket) on the local computer.

What we’re talking about here is user access to the mysql databases when they’re logged directly into the server computer. In other words, connections from other computers (hosts) won’t be affected by the state of this file. However, if you’ve locked down the mysql directory as I’ve specified above, local users aren’t going to be able to access the socket file, so they won’t be able to access the mysql databases locally.

There are two basic solutions if you need to allow this kind of access. The first is to open the mysql directory back up:

chmod 755 /usr/local/mysql

This is not so great, because it allows local users to see files and directories in the mysql directory. The better solution is to compile the mysqld package with the socket option set to place the socket file outside the /usr/local/mysql directory. At the configure stage, command:

./configure –with-unix-socket-path /usr/local #or a location of your choosing

 

Follow this procedure. First, start the mysql client:

mysql -u root -p

Eliminate anonymous accounts (accounts with no user name) In the client, command:

USE mysql;
DELETE FROM user WHERE User=”;
#That’s a pair of single quotes, enclosing nothing
FLUSH PRIVILEGES;

Check for accounts with no password:

SELECT * FROM user WHERE Password=”;

Any accounts listed have no password. Correct this:

UPDATE user SET Password=PASSWORD(‘new_pass’) WHERE User=’user_name’ AND Host=’host_name’;

Or, more succinctly:

SET PASSWORD FOR ‘user_name’@’host_name’=PASSWORD(‘new_pass’);

Then run a FLUSH PRIVILEGES command.

Use hostname wildcards ( % ) sparingly, if at all:

Specifying a host from which a user must connect is a critical element of MySQL security. Use this specification. This, for instance, is a “quick and dirty” (or easy) way to let the user peter connect from any host:

GRANT SELECT ON albums.* TO peter@’%’ IDENTIFIED BY ‘comes_alive’;

It’s also a great way to get cracked, giving crackers lots of potential points of entry to break into (any host that can reach your database server). If you have dozens or hundreds of hosts, you may be forced into it:

GRANT SELECT ON albums.* TO peter@’%.library.local’ IDENTIFIED BY ‘comes_alive’;

But note that in this example the wildcard restricts connections to a local domain, unlike the potentially-disastrous first example. This means you need to audit your accounts for hostname wildcards. But how do you search for a wildcard character that occurs anywhere in a hostname? It’s a little trickier than it might seem, but this will work:

SELECT * FROM user WHERE Host LIKE ‘%\%%’;

Think about the LIKE clause here. Now, examine any results of this query. You’ll need to evaluate on a case-by-case basis whether the wildcard is warranted or not. Perform an UPDATE query on any accounts you can configure more tightly.

But this won’t be enough in itself. You’ll need to perform a similar search on your grant tables as well, since those contain similar user/hostname strings, and UPDATE them as well.

As always, once you’re done, FLUSH PRIVILEGES.

Don’t Grant Global Privileges

Global privileges (to every database and every table) are granted like this:

GRANT privileges ON *.* TO user@host;

The most dangerous grant you can perform is:

GRANT ALL ON *.* TO user@host;

Don’t do this.

For one thing, granting ALL allows this user FILE privileges, so he can write from files to any database, or to files, thus stealing the database. For another, ALL includes SHUTDOWN, which lets this user do exactly that to the database.

Use REVOKE to remove privileges:

REVOKE FILE ON *.* FROM user@host;
REVOKE ALL ON *.* FROM user@host;

REVOKE ALL has one hole: it does not revoke GRANT privileges (which a user could use to re-grant themselves privileges):

REVOKE GRANT OPTION ON *.* FROM user@host;

 

There are several of these. The global option file is /etc/my.cnf. It must be owned by root so root can call the server startup:

chown root /etc/my.cnf

It must also, however, be readable by client programs. Use:

chmod 644 /etc/my.cnf

Every user can have their own /home/username/.my.cnf file as well. Just because it’s a “dot” (hidden) file doesn’t mean it’s protected; likewise its location in the user’s home is no real assurance of protection either. First, as the admin (root or other admin account), look at the file’s ownership:

ls -la /home/username/.my.cnf

Make sure the file is owned by the user, and the user’s group. Modify as necessary. Next, make the file inaccessible to everyone else:

chmod 600 /home/username/.my.cnf

Better yet, clean up configuration files that your users have left world-writable:

find /home -name .my.cnf ! -type l -ls | awk ‘{print $11}’ | xargs chmod 600

Or go one better and correct all files that have been left world-writable, which is a good exercise to perform from time to time in any multi-client environment:

find /home -perm -2 ! -type l -ls | awk ‘{print $11}’ | xargs chmod 600

How do you run a command like this on a timed basis? Glad you asked.

Use a file browser or a terminal window, whichever you’re most comfortable using. Go to the directory /etc/cron.weekly. (Notice that there are cron.daily, cron.hourly and cron.monthly directories, too.) If you put a script here, it will run once a week.

The smart thing to do is start from an existing script. You should have a yum.cron script, for instance; open this one or whatever you have.

You’ll see the all-important “she-bang” at the top, in most cases:

#!/bin/bash

or

#!/bin/sh

Save this script with a new name: fixfile.cron, for instance. Now all you have to do is erase the current contents (after the shebang) and add the command we used above. Save again and exit. You’re done. This script will run every week.

Would you like to be notified that the script has run? Just add this:

mail -s “File correction script” root@localhost <<EOF
“The file correction script ran at “
date

EOF

(This does require your server to have mail service available.)

 

Note that this modification should also be performed on any SQL utilities as well, for instance phpMyAdmin; these utilities must be owned by the mysql user and group.

 

Now, set the GUID and SUID bits (see this lecture if you’re unfamiliar with this concept) so that anything, anything, created in these directories is owned by the mysql user and the mysql group:

chmod g+s /usr/local/mysql/

chmod g+s /usr/local/mysql/data/

chmod u+s /usr/local/mysql/

chmod u+s /usr/local/mysql/data/

 

See the PHP section for an example of using an included, protected database connection file.

 

See the PHP Security section about using Magic Quotes, and the addslashes() and mysql_escape_string() functions.

MySQL II : GUI Tools

GUI Tools

MySQL AB also offers their own GUI tools, available (as usual) under their dual-license model. See http://www.mysql.com/products/tools/.

Administrator is similar to phpMyAdmin, and is under rapid development.

Try it:
Download and install the Administrator.

Download the RPM file and use the GUI interface to install it.

You will then need to run MySQL Administrator with this command:

/usr/bin/mysql-administrator

The Query Browser gives you a terrific application for developing and optimizing queries.

Try it:
Download and install the Query Browser.

The Migration Toolkit is extremely valuable for moving existing databases from proprietary platforms to MySQL.

This interesting application is in the alpha development stage. Keep track of it at http://forums.mysql.com/list.php?113 . It promises to bring many “Access-like” features to MySQL, including a relationships-diagraming utility.

Navicat offers terrific management tools, including backup and restore functionality, data import and transfer, scheduled tasks and a report builder.

Visit http://www.navicat.com/ and take the Feature Tour.

My personal favorite MySQL GUI administration tool is phpMyAdmin (http://www.phpmyadmin.net/home_page/).

This is a web-based application that you’ll install on your web server (or your ISP’s server – yes, you can do this).

Try it:
Download and install this tool in a subfolder of your web root.

• Download,

• Extract:

tar -zxvf phpMyAdmin-<version>.tar.gz

• Move this package to your web root (/var/www/html/ in most cases):

mv phpMyAdmin-<version> /var/www/html/

• Rename this directory to something useful, like admin:

mv phpMyAdmin-<version> admin

• Descend into this directory:

cd admin

• Copy the admin/libraries/config.default.php file to admin/config.inc.php:

cp libraries/config.default.php config.inc.php

• Now edit this file and set up host name, user name, and password.

Now go to your local host’s web site.

• Is your web server running? Do you need to configure anything? Where do you do this?

• Do you get any errors?

• Is php installed? (How do you check?)

• If it is, but you still get an error, what other package will you need to install? (Hint: check installed packages in the Fedora 4 GUI.)

• If you need to, install any other necessary packages.

See the documentation at http://www.phpmyadmin.net/documentation/.

U. Washington provides some nice installation notes at http://www.washington.edu/computing/web/publishing/phpmyadmin.html.

I’m providing two template files, htaccess and htpasswd, so you can build your own password-protection files. Copy them into your admin/ folder, then rename them so that each has a dot at the beginning of its name (making it a hidden file): .htaccess and .htpasswd.

Next, generate a password file using htpasswd. Run the program from the command line, with the user you want to add:

htpasswd glenn

Then you’ll need to update the path to your admin/ folder in the .htaccess file.

For further information about htpasswd, issue the command:

man htpasswd