Why can't I enter a NULL value using Numeric or Date type in Postgresql?

Question

I'm doing a little application for the work and it includes a form. When the person using the form doesn't put a value in a data type "numeric" (lets say a PIN number) or even a date in one of my "date" Data Type fields (lets say his anniverssary), it returns me with the errors as follow:

Error Type – Type d'erreur _Microsoft OLE DB Provider for ODBC Drivers (0x80004005)_ ERROR: invalid input syntax for type numeric: ""; Error while executing the query

Error Type – Type d'erreur _Microsoft OLE DB Provider for ODBC Drivers (0x80004005)_ ERROR: syntax error at or near ")"; Error while executing the query

So it seems that when the person using the form enters nothing, it returns the string "empty" that is "". Why can't the numeric type and data type read that as a NULL entry? These fields are not mandatory and so I need to have them be sometimes blank.

How can I make it possible? Is their a way to keep using "numeric" and "date" type so that when the user enters nothing in those fields, the table fills with a blank case insted of giving me this error?

Here is my sql statement :

trsConn.EXECUTE "INSERT INTO ""TRS"".employeetbl ( "& _
  """firstName"", " & _  
  """lastName"" , " & _  
  """detContract"", " & _  
  "sle, " & _  
  """posNumber"" "& _  
  ") VALUES (" & _  
  "'" & Request.Form("empFirst") & "', " & _**  
  "'" & Replace(Request.Form("empLast"), "'", "`") & "', " & _  
  "'" & Request.Form("dateContract") & "', " & _
  "'" & Request.Form("sle") & "', " & _  
  "'" & Request.Form("posNum") & "');"  

(The posNum and dateContract are both respectivly of type "numeric" and "date"

Thanks so much for the help. Looking forward for hearing what you geniuses have to say.

Answer 1

The concept of NULL in SQL is pretty muddled and inconsistent ... but it's very clear that '' is distinct from NULL.

'' isn't NULL, it's '', the empty string. You can't convert it to a date, number, etc:

regress=# SELECT CAST('' AS DATE);
ERROR:  invalid input syntax for type date: ""
LINE 1: SELECT CAST('' AS DATE);
                    ^
regress=# SELECT CAST('' AS NUMERIC);
ERROR:  invalid input syntax for type numeric: ""
LINE 1: SELECT CAST('' AS NUMERIC);
                    ^

Some products - notably Microsoft Access and old versions of MySQL - are confused about that matter. NULL is NULL, '' is the empty string; they aren't the same thing. You can't convert one to the other.

So it seems that when the person using the form enters nothing, it returns the string "empty" that is "". Why can't the numeric type and data type read that as a NULL entry? These fields are not mandatory and so I need to have them be sometimes blank.

That's your application's job. When your app it sees the empty string come in on a form field for a numeric, date, or similar, it should send NULL to the database, not ''. That's normally a routine part of converting data from user input before it's supplied to the database. It is vital that you do such conversion; you should never just send values from the user straight to the database.

A quick search suggests that asp classic uses null or undefined as its null values; you should be able to pass them into your prepared statements when something is null.

The fact that you get a syntax error after the error about '' suggests that you're building your SQL statements as strings, not using prepared statements with placeholders. (Thanks JayC for the SO question ref). This is begging for SQL injection; in other words your application is critically insecure. Imagine what happens if the user enters the "date":

2012-01-01'); DROP SCHEMA public;--

and your app happily turns that into

INSERT INTO sometable (blah, blah, blah) VALUES (1, 2, DATE '2012-01-01'); DROP SCHEMA public;--');

The DROP SCHEMA then merrily executes and whoops, splat, there goes your database. That's just the dumbest, simplest kind of SQL injection attack too.