-1

I'm assuming this should be very simple, but I can't really understand why this error happens.

I have a database like the following:

Field: id
Type: int(4) (AUTO INCREMENT)

Field: Name
Type varchar(30)

Field: Age
Type: int(3)

Everytime I leave the Age field empty on my html form, it gives me an error while trying to INSERT into the DB.

$name = $_POST['name'];
if (!isset($_POST['age'])) {
    $age = 0;
} else {
    $age = mysql_prep($_POST['age']);
}

INSERT INTO tage (name, age) VALUES ('$name', $age)

Printing the query, it gives me:

INSERT INTO tage (name, age) VALUES ('John',)

The error I get is the following: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '')' at line 1.

Of course this is an example database.

If I actually type 0 (the number zero) on my html form, it works fine, but if I leave it empty, the error appear and it seems I can't assign 0 to the var $age.

EDIT: It's worth to mention that I did set the field age on my database with the default value to NULL (using phpmyadmin).

Any feedback is appreciated.

Just a final EDIT so people are not under the wrong impression, this is the function mysql_prep() used on the sample code:

function mysql_prep( $value ) {
    global $connection;
    $magic_quotes_active = get_magic_quotes_gpc();
    $new_enough_php = function_exists( "mysqli_real_escape_string" );
    if( $new_enough_php ) { // PHP v4.3.0 or higher
        if( $magic_quotes_active ) { $value = stripslashes( $value ); }
        $value = mysqli_real_escape_string( $connection, $value );
    } else { // before PHP v4.3.0
        if( !$magic_quotes_active ) { $value = addslashes( $value ); }
    }
    return $value;
}

And the problem is now solved, and the problem was that I was under the wrong impression that empty fields weren't posted.

Rafael Schneider Pereira
  • 19
  • 7
  • 1
    It is most likely set, but is [`empty()`](http://php.net/empty). – drew010 Mar 31 '18 at 01:23
  • Okay, so from your comment I assume when you set a $var=0 it actually makes the var to be empty, so how do I fix this then? – Rafael Schneider Pereira Mar 31 '18 at 01:26
  • no, he means $_POST['age'] is ''. isset returns true. use empty() instead. – Garr Godfrey Mar 31 '18 at 01:29
  • You should implement some additional error checking, as @drew010 suggested (eg. empty string, non-numerical values). For starters, take a look at [is_numeric](http://www.php.net/is_numeric), [intval](http://www.php.net/intval) and [empty](http://www.php.net/empty). – ccKep Mar 31 '18 at 01:29
  • 1
    Please [don't use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1); the `mysql_*` functions are outdated, [deprecated](http://us3.php.net/manual/en/intro.mysql.php), and insecure - they have been removed entirely from modern versions of PHP (version 7.0 and higher). Use [`MySQLi`](http://us3.php.net/manual/en/book.mysqli.php) or [`PDO`](http://us3.php.net/manual/en/intro.pdo.php) instead. – elixenide Mar 31 '18 at 01:41
  • 1
    Also, you are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). – elixenide Mar 31 '18 at 01:41
  • First of all, the "mysql_prep" isn't mysql, it's a function I created. I use msqli, this is just an example code, but I use a function with that name. As for SQL injection, you are right and I 100% agree with you, but as I said, this is a sample code just to figure the reason for me to be getting an error, not the actual thing. Problem was solved, and thanks for the (minus), I guess. – Rafael Schneider Pereira Mar 31 '18 at 19:57

1 Answers1

2

If you don't enter a value in a form, it still sends up the $_POST['age'] but it is simply empty.

You should change your code like this:

if (empty($_POST['age']) || !is_numeric($_POST['age'])) {
    $age = 0;
} else {
    $age = (int) mysql_prep($_POST['age']);
}

Then look into using prepared queries as $name = $_POST['name']; is open to SQL injection.

Lawrence Cherone
  • 46,049
  • 7
  • 62
  • 106