Log in

View Full Version : Resolved NULL Error When Adding to Database


SChaput
10-16-2008, 12:11 AM
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:

$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:

$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:
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
<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
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:
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.