Getting started with SQL (part 3).

By: L33tdawg

As promised, I shall wrap things up this month with some examples of starting an SQL
database and testing the connections and such in PHP If you're wondering what the
heck I'm going on about perhaps it would be wise to read up on part 1 and part 2 before
continuing on. For those of you that have stuck with me this far, let's get to it...

Creating a sample database

For the purpose of this article, we'll create a simple database of users. This could perhaps
be used for POP3 authentication or perhaps as a building block for user sign ups on your
website. If you're already logged into MySQL, then you'll only need to issue the
following:

CREATE DATABASE users;

If you've already logged out, then the quickest way to getting the same result would be :

/usr/local/mysql/bin/mysql u admin p users

We'll now create the password table in which we can store the basic user information:

CREATE TABLE password (
username varchar(8) not null,
crypt varchar(128) not null,
realnam varchar(64) not null,
shell varchar(20) not null
);

Note: You could also easily create the following entry in your favourite text editor, then
just cut and paste it or dump the data in.

Adding Data.

Now that we've got ourselves a database and table, its time to start feeding in values into
the database. Since we created a dbuser user a while back, perhaps it's a good time to log
out and log back in as the dbuser we created.

/usr/local/mysql/bin/mysql u dbuser p users

Now you can add a user to the password table and very that the data is there with a select
query. To do this, just issue the following:

INSERT INTO password VALUES('username',md5('password'), 'First User',
'/bin/bash');

To take a look at the values within your table, just issue a simple:

SELECT * from password;

There are a fair number of permutations you can use when selecting data from the table,
however it is beyond the scope of this article. As such I would recommend reading the
manual or better yet, getting yourself a copy of a good book.

The script

Now having a database and dumping data into it manually is going to be a real pain.
Would it not be easier to have a web based interface from which to interact with making
for easier data entry? Well that's not going to be a problem. One of the great things about
MySQL is that it interfaces with quite a fair number of different programming languages
from C to PERL and (without a doubt), PHP. Now since my background is inherently
PHP with a bit of C thrown in, I thought it would be best to provide all of you with a
working PHP script for you to play around with. Granted, these files don't really do much
else apart from providing a means to input data and display them back to the user, it will
be sufficient as an example.

The input form.

The source code for the form is here.
Now that you've got yourself a form and such, it's time to move on to the file that will actually process the values from the
form and put them into the database. The sample script is here. Do note that you'll need to un-comment the code
before you can use it *grin*.

Well that's pretty much it! You've now got yourself a database, and two files to start dumping in data. Now of course these
examples are very simplistic to say the least, but they are the building blocks to building something bigger and better. As
with most things in life. I recommend getting a good PHP book (if that's the language you're going to use)... Even if you
consider yourself to be a uber hax0r, it always helps having a reference guide on hand for those those 'hard to remember'
commands and syntax. Good luck, and keep on coding.

Peace.
L33tdawg.

1.)
File removal: How to be sure - madirish

2.)
NT Security Tools - madirish
3.)
Getting Started with SQL (part 3) - L33tdawg
4.)
Anatomy of Brute Force Attacks - madirish
5.)
IIS Script Directory Exploit - madirish
6.)
Another one bites the dust - L33tdawg
7.)
Using PHP Securely - SecuriTeam