INSERT creating 'null row'

shanep

Hi All

This is my first post so please go easy...

I've built a small site for my fiance's pr agency. It's nothing fancy at all, but at present when they get a new job I simply use phpMyAdmin to insert the various records required by the scripts I wrote to display the jobs.

That's becoming a bit of a bind so I wanted to create a secure area for her to be able to insert the relevant data herself. I read up on sessions/cookies etc. and this morning started on a serious of scripts that would allow me create users who would be able to access this secure area.

The scripts concerned are:

'newuserinsert.php' which is basicaly just a form for collecting the data.
'newuserprocess.php' which does the data validation and either re-directs back to 'newuserinsert.php' (if the data is incorrect) or inserts the data into the database.
'newuserconfirm.php'. not built yet but will confirm to the user their account has been created.

It was going fine until about an hour ago when I hit the following problem:

if the 'tbl_users' table in my database contains a row with null values in all fields (except the unique identifier) then the scripts on these pages work as required.

HOWEVER... if I delete the "null row" from the database and re-submit the form the error checking in page 2 seems to get by-passed entirely and a new 'null row' is created in the table.

I've posted below the scripts, and I hope that makes sense, but if not please let me know if you need clarification on anything.

Thanks in advance
Shane

P.S. please ignore the lack of password encryption as that is the next thing on the list.

NEWUSERINSERT.PHP

<?php
session_start();
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">

<html>

<BODY>

<?php
//print header
require('../includes/t_header_include.php');
?>

<div class="h01">Create New User</div>

<?php
echo session_id();

if (isset($SESSION["errorString"])&& $SESSION["errorString"] != "")
{
echo $_SESSION["errorString"];
}
else
{
echo "<p>Please enter the details requested below to create a new user account.</p>";
}
?>

<form action='t_newuserprocess.php' method='POST'>
<fieldset>
<p><label for="formname">Enter your First Name</label> <input type='text' name='name' id='formname'/></p>
<p><label for="formuser">Choose a Username</label> <input type='text' name='user' id='formuser'/></p>
<p><label for="formpass">Choose a Password</label> <input type='password' name='pass' id='formpass'/></p>
<p class="submit"><input type='submit' value='Create User'/></p>
</fieldset>
</form>

<?php

</div>

<?php
require('../includes/t_footer_include.php');
?>

</div>
</BODY>
</HTML>

NEWUSERPROCESS.PHP

<?php
session_start();

// includes
include '../includes/error.php';
include '../includes/db.php';

// clean session variables
$_SESSION["errorString"] = '';

// clean and trim the POSTed values
$HTTP_POST_VARS['name'] = trim(clean($HTTP_POST_VARS['name'], 30));
$HTTP_POST_VARS['user'] = trim(clean($HTTP_POST_VARS['user'], 30));
$HTTP_POST_VARS['pass'] = trim(clean($HTTP_POST_VARS['pass'], 32));

// validate the POST values
if (empty($HTTP_POST_VARS['name']))
{
$SESSION['errorString'] .="\n<BR>You must enter your first name.";
}
if (empty($HTTP_POST_VARS['user']))
{
$SESSION['errorString'] .="\n<BR>You must choose a username.";
}
if (empty($HTTP_POST_VARS['pass']))
{
$_SESSION['errorString'] .="\n<BR>You must choose a password.";
}

if (!($connection = @ mysql_connect('localhost', $username, $password)))
die("Could not connect to the database");

if (!mysql_select_db($database, $connection))
showerror();

$d = $HTTP_POST_VARS['user'];

$query = "select userID from tbl_users where userName='$d'";
$result=mysql_query($query);
$num=mysql_numrows($result);

//close db connection
mysql_close();

if ($num != "0")
{
$_SESSION['errorString'] = "\n<BR>The username '$d' is already in use. Please choose another username.";
}

// check if there were any errors
if (!empty($_SESSION['errorString']))
{
// there are errors. Re-direct the user to the create user page and advise
// them of the errors.
$HTTP_POST_VARS['name'] = "";
$HTTP_POST_VARS['user'] = "";
$HTTP_POST_VARS['pass'] = "";
header("Location: t_newuserinsert.php");
}

// if we made it here then the data is valid

if (!($connection = @ mysql_connect('localhost', $username, $password)))
die("Could not connect to the database");

if (!mysql_select_db($database, $connection))
showerror();

// declare variables
$a = $HTTP_POST_VARS['user'];
$b = $HTTP_POST_VARS['pass'];
$c = $HTTP_POST_VARS['name'];

// create insert query
$query = "INSERT INTO tbl_users
set userID = NULL, " .
"userName = '$a', " .
"userPass = '$b', " .
"firstName = '$c'";

// run the query
if (!(@ ($query, $connection)))
showerror();

// find the new userID
$userID = mysql_insert_id();

//close db connection
mysql_close();

// redirect to the new user welcome page
$HTTP_POST_VARS['name'] = "";
$HTTP_POST_VARS['user'] = "";
$HTTP_POST_VARS['pass'] = "";
$_SESSION["errorString"] = '';
header("location: t_newuserconfirm.php?userID=$userID");

TBL_USERS

This simply contains 4 fields - userID, userName, userPass and firstName.

paulnaj

Hi,

You need to get yourself upto speed with some basic debugging techniques.

Make sure error_reporting is set whilst building. Put ...

error_reporting(E_ALL);

... at the top of the script you are trying to fix. You can revert to the default later by just commenting it out. The default is E_ALL ^ E_NOTICE, which means that notices are left out. Notices can be very handy for debugging.

Before you allow the code to actually insert/delete/update stuff into/from/out of the db, take a step back and just echo out the SQL string to the page and exit. You'll see if the SQL is well-formed or not and can even cut-n-paste it into phpmyadmin to see what happens.

It might show that $HTTP_POST_VARS isn't defined and you should be using the $_POST array instead. This is the case for newer versions of PHP.

Hope that helps.
P.

shanep

Hi Paul, and thanks for the quick response.

I'd actually tried ECHOing the SQL and all the POSTed values prior to posting this message. All POSTed values are coming through correctly and the SQL reads:

INSERT INTO tbl_users set userID = NULL, userName = 'rty', userPass = 'rty', firstName = 'rty'

which I'm fairly sure is correct...

The thing that gets me is the logic of what's going wrong, e.g.

If I have a row in tbl_users in which all fields (save the unique identifier) are unpopulated then the script in newuserprocess.php functions as expected.

If I do not have that 'null row' in tbl_users when I submit the form (and hence POST the values to the script) it actually carries out all the data validation (I can see this as I have ECHOed $_SESSION["errorScript"] and it does contain values) yet still inserts a 'null row' into the database. This happens irrespecive of what data I pass the script via the form...! I just can't get my head around it.

If I then go back (i.e. with that 'null row' now once again present in tbl_users) and re-submit the form (with or without changing the data) the script functions as expected again! It seems it will only function correctly if there is a 'null row' in the tbl_users table.

Am I missing something really obvious here or is this a feature of PHP/MySQL?

Thanks
Shane

Weedpacket

INSERT INTO tbl_users set userID = NULL, userName = 'rty', userPass = 'rty', firstName = 'rty'

It looks like you're getting the syntax for INSERT and UPDATE mixed up.

dagon

Weedpacket;10901886 wrote:
It looks like you're getting the syntax for INSERT and UPDATE mixed up.

that's valid for insert and update, insert has 3 syntax versions.

Weedpacket

dagon wrote:
that's valid for insert and update, insert has 3 syntax versions.

Huh, well, not in my DBMS; I guess the SQL-standard two were considered enough.

One thing I notice is that if $errorString is not empty, then it clears out the $HTTP_POST_VARS fields and then calls location("Header:..."). Then, because there is no exit() anywhere to be seen after that, it proceeds to open a database connection (that had just been closed a few milliseconds earlier) and performs an insert.

In other words:

// if we made it here then the data is valid ... or maybe not.

I don't see why the $HTTP_POST_VARS (or, as it has been called since 2001, $_POST, with the former going away soon) needs to be cleared at all.

shanep

Thanks for both your inputs guys!

They've helped in rectifying the issue as I can now delete the 'null row' from the database, browse to the newuserinsert.php page, submit the form (with or without values supplied) and the data validation now fnctions correctly! hoorah!!!!!

because I'm not 100% sure what worked (though I've a feeling it was the missing exit()😉 I've just copied the revised script in below for anyone else who finds themselves stuck in this situation.

Thanks again for all the posts!

Cheers
Shane

P.S. the include 'functions.php' i new because there was a function that I had in 'db.php' that seemed to be causing issues with other pages so i dropped it into it's own include file so I could exclude it from those other pages.

Also - I'm using $HTTP_POST_VARS 'cos I've just checked the manual I'm teaching myself from and it's a firstedition (2002). better go get the latest version!

NEWUSERPROCESS.PHP
<?php
session_start();

// includes
include '../includes/error.php';
include '../includes/db.php';
include '../includes/functions.php';

// clean session variables
$_SESSION["errorString"] = '';

if (!($connection = @ mysql_connect('localhost', $username, $password)))
die("Could not connect to the database");

if (!mysql_select_db($database, $connection))
showerror();

$d = $HTTP_POST_VARS['user'];

$query = "select userID from tbl_users where userName='$d'";
$result=mysql_query($query);
$num=mysql_numrows($result);

if ($num != "0")
{
$_SESSION['errorString'] = "\n<BR>The username '$d' is already in use. Please choose another username.";
}

// check if there were any errors
if (!empty($_SESSION['errorString']))
{
// there are errors. Re-direct the user to the create user page and advise
// them of the errors.
header("Location: t_newuserinsert.php");
exit();
}

// if we made it here then the data is valid

// declare variables
$a = $HTTP_POST_VARS['user'];
$b = $HTTP_POST_VARS['pass'];
$c = $HTTP_POST_VARS['name'];

$e = updatePassword($a, $b);

// create insert query
$query = "INSERT INTO tbl_users
set userID = NULL, " .
"userName = '$a', " .
"userPass = '$e', " .
"firstName = '$c'";

// run the query
if (!(@ ($query, $connection)))
showerror();

// find the new userID
$userID = mysql_insert_id();

//close db connection
mysql_close();

// redirect to the new user welcome page
$_SESSION["authUser"] = $a;
header("location: t_login.php");

laserlight

Post your well indented PHP code in [php][/php] bbcode tags.

I suggest changing this:

// create insert query
$query = "INSERT INTO tbl_users
set userID = NULL, " .
"userName = '$a', " .
"userPass = '$e', " .
"firstName = '$c'";

To this:

// create insert query
$query = sprintf("INSERT INTO tbl_users (userName, userPass, firstName) VALUES ('%s', '%s', '%s')",
    mysql_real_escape_string($a),
    mysql_real_escape_string($b),
    mysql_real_escape_string($c));

Basically, I changed the syntax of the SQL statement to a more standard version. I also call mysql_real_escape_string() to escape the values to be entered to the database. This is to avoid SQL injection. I removed the userID column from the list on a hunch that it is auto-incrementing (it does not make sense to insert a NULL userID for a valid row otherwise), so you might as well leave it out and the database engine will do its job.

Oh, and you should use variable names that are more descriptive than $a, $b and $c. (You even had a typographical error where you used $e instead of $b.)

cyberlew15

I'm in agreement with Laser on this one, btw if this is resolved please mark it resolved also weedpacket is right for best practice specify the field names to go into when coding it can be helpful when debugging applications.