NULL Error When Adding to Database

**SChaput** · 10-16-2008, 12:11 AM

Code:

Could not insert data because Incorrect integer value: 'NULL' for column 'id' at row 1

I get this code when i try and run the register form on my website, which allows user to create a user name and password.

this is the area of code i believe is failing:

Code:

$insert = mysql_query("insert into $table values ('NULL', '".$_POST['fname']."', '".$_POST['username']."', '".$encrypted_password."', '".$_POST['email']."' , '".$_POST['loc']."')")
or die("Could not insert data because ".mysql_error());

And this is the current layout of the table:

Code:

$create = "create table $table (
id smallint(5) NOT NULL auto_increment,
realname varchar(30) NOT NULL,
username varchar(30) NOT NULL,
password varchar(32) NOT NULL,
email varchar(30) NOT NULL,
location varchar(30) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY username (username)
);";

This setup works fine on one database host, however it does not work on the one im trying to switch too. Am i missing something?
Thanks.

**Twey** · 10-16-2008, 04:12 AM

Exactly what it says: you're trying to insert the string 'NULL' into an integer column.

**SChaput** · 10-16-2008, 04:34 AM

so how come it works on certain dasebase servers and not others?
what do i need to alter to make it work on the current one?

**Twey** · 10-16-2008, 06:07 AM

Perhaps your other database system attempted to perform a type conversion. Your query should look like this:

Code:

function s($n) {
    return mysql_real_escape_string($_POST[$n]);
}

mysql_query(sprintf('insert into %s (realname, username, password, email, location) values (\'%s\', \'%s\', \'%s\', \'%s\')'),
                    s('fname'),
                    s('username'),
                    $encrypted_password,
                    s('email'),
                    s('loc')))
  or die(mysql_error());

Also note that you failed to escape your inputs in the original code, resulting in an SQL injection vulnerability. I strongly recommend switching to the new PDO interface.

**SChaput** · 10-16-2008, 02:57 PM

Code:

<script language="JavaScript">
function redirect() {
  	location.href='~chaputs1';
  }
</script>
<?php 

include("config.php"); 

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());

// check if the username is taken
$check = "select id from $table where username = '".$_POST['username']."';"; 
$qry = mysql_query($check) or die ("Could not match data because ".mysql_error());
$num_rows = mysql_num_rows($qry); 
if ($num_rows != 0) { 
echo "Sorry, there the username <b>$username</b> is already taken.<br>";
echo "<a href=reg.php>Try again</a>";
exit; 
} else {

// insert the data
$encrypted_password = md5($_POST['password']);
function s($n) {
    return mysql_real_escape_string($_POST[$n]);
}

mysql_query(sprintf('insert into %s (realname, username, password, email, location) values (\'%s\', \'%s\', \'%s\', \'%s\')'),
                    s('fname'),
                    s('username'),
                    $encrypted_password,
                    s('email'),
                    s('loc')))
  or die(mysql_error());

// print a success message
echo "Your user account has been created!<br>"; ?>
<body onLoad="setTimeout('redirect()', 2000);">
<?php
echo "Redirecting..."; 
}

?>

I put your new section of code in place of my insufficient one. And am still getting errors. When i run the code as is i get this error:

Parse error: syntax error, unexpected ')' in /www/Final/register.php on line 39

if i try and correct that i get the following error.

Warning: sprintf() [function.sprintf]: Too few arguments in /www/Final/register.php on line 34

Warning: Wrong parameter count for mysql_query() in /www/Final/register.php on line 39

Do i need to alter my table to get this new code working? Like remove the ID row?
Thanks

**SChaput** · 10-16-2008, 04:55 PM

Code:

function s($n) {
    return mysql_real_escape_string($n);
}

mysql_query("INSERT INTO users SET 
			realname = '" . s($fname) . "', 
			username = '" . s($username) . "', 
			password = '" . $encrypted_password . "', 
            email = '" . s($email) . "',
            location = '" . s($loc) . "' 
			", $link) or die(mysql_error());

All set. thank you.

**Twey** · 10-17-2008, 01:04 AM

Oh, I forgot to add the table name:

Code:

function s($n) {
    return mysql_real_escape_string($_POST[$n]);
}

mysql_query(sprintf('insert into %s (realname, username, password, email, location) values (\'%s\', \'%s\', \'%s\', \'%s\')',
                    $table,
                    s('fname'),
                    s('username'),
                    $encrypted_password,
                    s('email'),
                    s('loc')))
  or die(mysql_error());

sprintf() is generally preferable to string interpolation, especially when it involves some really nasty concatenation like the above. It's faster and more readable.

Thread: NULL Error When Adding to Database

Thread Tools

Search Thread

NULL Error When Adding to Database

Bookmarks

Bookmarks

Posting Permissions