SQL and phpMyAdmin


Andrew Cantino


Introduction

This lesson will explore databases and database interaction, as well as some important database security practices. Lets start with SQL.

SQL

SQL stands for Structured Query Language. SQL is a standard method of interacting with databases, including MySQL, Ingres, Oracle databases, etc. SQL isn't very hard to learn, so I will only include the basics here. For an excellent, interactive introduction to SQL, please see sqlcourse.com.

Tables

A table in SQL is a collection of records organized into rows and columns. The column names for a given table are set before-hand. For example, a possible table, call it address_records, could have the first column be 'first_name', the second column be 'last_name', and the last column be 'address'. It might look like:

  address_records    
first_name last_name address  
andrew   cantino 370 lancaster ave
some   guy   123 fake st.

SELECT

The SQL SELECT statement is used to get records from a table. Here are some examples to get you started: 

# Prints out all of the first names
SELECT first_name FROM address_records;

# Prints out the whole table
SELECT * FROM address_records;

# Print out addresses of people whose first name is 'andrew'
SELECT address FROM address_records WHERE first_name = 'andrew';

# Print first and last names whose address contains 'lancaster;
SELECT first_name, last_name FROM address_records WHERE address LIKE '%lancaster%'

INSERT

To add to a table, do something like the following: 

INSERT INTO address_records
  (first_name, last_name, address)
  values ('Matt', 'Nocifore', 'Stokes');

UPDATE

UPDATE lets you modify existing records. 

UPDATE address_records SET address = 'HCA' WHERE last_name = 'cantino';
UPDATE address_records SET first_name = 'andrew', last_name = 'cantino' WHERE address = 'Stokes';

DELETE

DELETE removes records. 

DELETE from address_records;  # CAREFUL, this removes ALL records in address_records!!
DELETE from address_records WHERE address LIKE '%fake%';
DELETE from address_records WHERE first_name = 'some';

Learn more

MySQL

MySQL is a free, open source database server. MySQL is probably the most common SQL database software used on the Internet, and we will be using it to create an example database to play with. I will give a basic introduction here, but I highly recommend that you take the time to read this extropia SQL tutorial.

Creating MySQL databases and users with cPanel

MySQL has databases and users, much like UNIX has users and groups. We give certain users privileges on certain databases. Lets create a new database and a new user with permissions to use it.

Interacting with MySQL

There are two ways to work with our new database. We can connect on the command line and interact directly with MySQL, or we can use a program called phpMyAdmin to interact with MySQL via a web browser. First, I will show you how to use phpMyAdmin to create a table in our new database, then I will show you how to use MySQL directly on the command line.

Using phpMyAdmin

Using MySQL on the command line

Now, lets connect to MySQL from the UNIX shell. Login to your shell account and type: 

mysql <username>_testdb -u <username>_test -p
You will be prompted for the password for the <username>_test user. This is the password you entered above. You should see something like this: 
-jailshell-2.05b$ mysql acantino_testdb -u acantino_test -p
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32872 to server version: 4.0.22-standard

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

mysql>
This is the MySQL prompt -- rather like a UNIX prompt. You use the MySQL prompt to enter SQL commands. Lets try some: 
mysql> describe address_records;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| first_name | varchar(15)  |      |     |         |       |
| last_name  | varchar(15)  |      |     |         |       |
| address    | varchar(100) |      |     |         |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from address_records;
+------------+-----------+---------+
| first_name | last_name | address |
+------------+-----------+---------+
| andrew     | cantino   | hca     |
+------------+-----------+---------+
1 row in set (0.00 sec)

mysql> insert into address_records (first_name, last_name, address) values ('matt', 'nocifore', 'stokes');
Query OK, 1 row affected (0.00 sec)

mysql> select * from address_records;
+------------+-----------+---------+
| first_name | last_name | address |
+------------+-----------+---------+
| andrew     | cantino   | hca     |
| matt       | nocifore  | stokes  |
+------------+-----------+---------+
2 rows in set (0.01 sec)
Play around with the SQL commands that we learned before. When you're done, type quit.

MySQL and Perl

To connect to MySQL from Perl, we use the Perl DBI MySQL module. The DBI is a set of database interface modules for Perl. Here is an example script that uses DBI: 

#!/usr/bin/perl -Tw
use DBI;

$dbh = DBI->connect("DBI:mysql:acantino_testdb:localhost","acantino_test","<PASSWD>");

$sth = $dbh->prepare("select * from address_records");
$sth->execute;

while( ($fname, $lname, $address) = $sth->fetchrow() ) {
        print "'$fname $lname' lives at '$address'.\n";
}

$sth->finish;
$dbh->disconnect;

print "Done.\n";
Output: 
'andrew cantino' lives at 'hca'.
'matt nocifore' lives at 'stokes'.

The MySQL Account Maintenance panel kindly generates a Perl MySQL connection string for you. The one for our example database should be: 

$dbh = DBI->connect("DBI:mysql:<USERNAME>_testdb:localhost","<USERNAME>_test","<PASSWORD HERE>");

SQL Security

The main security issue with SQL is called the SQL injection attack. Let's say that you had the following code: (Don't do this!) 

$sth = $dbh->prepare("select * from address_records where name='$name'");
$sth->execute;
If $name is coming from a user's input, then they can do an SQL injection attack. What if they enter the following? 
something' OR '1'='1
The SQL query will look like: 
select * from address_records where name='something' OR '1'='1'
One always equals one, so this will return every record in address_records! Here's an even more destructive example. What if they enter: 
something'; DROP DATABASE;
As you can see, it's very important to escape any quote characters in variables you include in SQL commands. However, you can't reliably do this with regular expressions, so you need to use bound parameters, as will be described momentarily.

Reading more

For a definitive guide to SQL injection attacks, see SQL Injection Attacks by Example.

Doing SQL securely

To do SQL securely, it's best to use bound parameters. When you use bound parameters, your variables are passed to the SQL server separately from the SQL query string, so there's no way for an injection attack to occur. In Perl, bound parameters look like this (example from SQL Injection Attacks by Example): 

$sth = $dbh->prepare("SELECT email, userid FROM members WHERE email = ?;");
$sth->execute($email);
The question mark (?) is replaced by the value passed to the $sth->execute() function. You may use multiple question marks -- they will be replaced in order by the values passed to $sth->execute().

Suggested Assignments

Make your own databases

After you've completed Creating MySQL databases and users with cPanel, Using phpMyAdmin, and Using MySQL on the command line, try making your own databases, users, and tables. Here are some table suggestions:

MySQL and CGI

You should know enough now to make simple Perl CGI scripts that work with databases. Try to make one of these. I'll be happy to assist. Here are some ideas:

Don't forget about SQL injections -- make sure you're quoting everything.

Back to Course Index


This document was generated using AFT v5.094