I can't add 'null' value in MySQL 5.5

Question

When I try to INSERT values in my database, using the following code:

INSERT INTO obra (codInstituicao, codInventariadaPor, dataInventariada,nome,codEstadoConservacao, .......)  values (" . $codInstituicao ."," .  $_SESSION['id']. ", NULL, '" . $nome ."' , '" . $codEstadoConservacao . "', ......)

In the beginning of this code, is declared:

$codEstadoConservacao = (empty($_POST['estadoconservacao']) ?  "NULL" : $_POST['estadoconservacao']);

The TYPE of my tables is InnoDB. When I execute the INSERT, i got this error:

Incorrect integer value: 'null' for column 'codEstadoConservacao' at row 1

Instead of showing us how you build the query, show us the query just before you run it. That way, we can see exactly what's happening — Basic, Jun 06 '14 at 23:38
Your code is [vulnerable to MySQL injections](http://stackoverflow.com/q/60174/453331). — kba, Jun 06 '14 at 23:43

score 2 · Answer 1 · answered Jun 06 '14 at 23:23

2

You're inserting a literal string into the database. Remove the double quotes - e.g. use NULL instead of "NULL".

Also, you're prone to SQL injection with that query. You should use something like PDO to bind parameters to your SQL query.

answered Jun 06 '14 at 23:23

Mark

2,669
2
17
13

score 1 · Answer 2 · answered Jun 06 '14 at 23:31

Two major problems here.

First, if $codEstadoConservacao is NULL then it will insert 'NULL', as a string, literally the text NULL because of the quotation marks. It is not the same thing as MySQL's NULL.

Second, I see you grab this value from $_POST and build your SQL Query with it without fearing consequences. You are about to be victim of SQL Injection / Injeção de SQL.

You have to treat all and any user input accordingly to prevent that, using prepared statements, or some escape function such as mysql_real_escape_string() / mysqli_real_escape_string() etc. Look for the options available to whatever database connection method you are user.

score 0 · Answer 3 · answered Jun 06 '14 at 23:21

0

Don't wrap your NULL value in quotes:

, '" . $codEstadoConservacao . "',

should be

, " . $codEstadoConservacao . ",

answered Jun 06 '14 at 23:21

Joseph

5,070
1
25
26

I can't add 'null' value in MySQL 5.5

3 Answers3