Results 1 to 7 of 7

Thread: NULL Error When Adding to Database

  1. #1
    Join Date
    Feb 2008
    Posts
    90
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Default NULL Error When Adding to Database

    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.
    Last edited by SChaput; 10-16-2008 at 04:55 PM.

  2. #2
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Exactly what it says: you're trying to insert the string 'NULL' into an integer column.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  3. #3
    Join Date
    Feb 2008
    Posts
    90
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Default

    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?

  4. #4
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    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.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  5. #5
    Join Date
    Feb 2008
    Posts
    90
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Default

    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

  6. #6
    Join Date
    Feb 2008
    Posts
    90
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Default

    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.

  7. #7
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    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.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •