Check my tutorial please

[alexgeek]

I made a new tutorial for my site, on mysql, php, registering users, logging in, and checking if they are logged in, "basic user interface".
http://www.alexgeek.co.uk/test/tutorial.html
it's not finished yet,
but could someone have a general check at it for me.
Thanks

By the way I haven't put in the credits to the people who helped me and webforumz.com yet

[alexgeek]

Oh and I need to cut down all the text. I babble quite a bit in it.
I'm going to make two versions, the in depth one, this one.
then strip it down to just code and labels for a "straight to the point" tutorial

[ioncube]

Hi Alex

There are some typos in the wording, and the black on green is a bit hard to read and oppressive, but the biggest potential problem is that because there is no validation or quoting of the values for $_POST['user'] and particularly $_POST['pass'], it would be possible to use a malformed value for the password to force a row to be returned even of no row matches the username and password and then get to the logged in state.

The script also uses $_POST['username'] for the user rather than $_POST['user'] in some places, and this is incorrect based on the form spec.

So check carefully that your code actually works, and either add some validation to the password and username such that malformed values cannot change the query, or make a bold note that your example is totally insecure, explain why and how it can be fixed and say that you didn't do this for brevity. I suggest making it more secure though because examples that are insecure is one reason why we see production systems with major vulnerabilities.

[alexgeek]

thanks ioncube,
i hadn't got round to checking and testing the code yet

[ioncube]

Originally Posted by alexgeek

i hadn't got round to checking and testing the code yet

Not bad then all things considered

A download link to a zip and tar.gz archive with the files in would probably be appreciated by someone wanting to test it as it would save them cutting and pasting. As well as adjusting colours, a smaller font might make it easier to read too.

With the database schema, you have NOT NULL for the first column, but not on the others. It's usual to specify all columns as NOT NULL as it saves one bit of storage per field and is marginally more efficient. Having a null flag is sometimes useful so that you can distinguish between the default value, e.g. 0 or empty string, and really having no value, but almost always it's not required.

There's also no primary key but this is required if using auto_increment. Adding PRIMARY KEY(id) to your create table would fix that.

Adding UNIQUE will create a unique index, but with such a long key this is inefficient and unnecessary as you're checking for this in the code. Similarly for adding UNIQUE on email. You can create indices for string fields, but this is usually to improve efficiency of searching and not to enforce uniqueness, and the length of the key would be limited to a more reasonable size.

e.g. CREATE INDEX by_username ON users (username(4));

to create the index based on the first 4 characters of the field username.

Assuming that the unique index is gone, there is a potential race condition due to lack of table locking. What could happen is that one request looks up username XXX, determines that it doesn't exist, and before the code gets to do the insert, another request has done the same for the same username and has performed the insert. There are now two entries for the same username. Of course this is highly improbable and we might go so far as to say that it would never happen, but problems with race conditions stem from people being lazy and not getting into the habbit of looking for such issues so that when the time comes for them to code a solution where a race condition is actually quite possible, they are unaware and the design flaw creeps in.

The solution is to get a write lock on the username table, do the lookup, insert if the username is unique, and then unlock tables. Acquiring the write lock will only succeed for one request and will block others (they wait until the lock is released), so you prevent two requests both determining that the same username doesn't exist and trying to insert.

[alexgeek]

thanks for all that
and i like the idea of archiving the files
do you happen to know if there is some program that can't automate mysql commands you have set for it?

[ioncube]

One way is to use the mysql CLI client and the command "source". "source" takes a file with queries.

At least with MySQL 3 there doesn't seem to be a command line option to the client that does the equivalent of source but that's no real problem.