Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
                Wow. Does "ASP classic" support placeholders or prepared statements? I would suspect one issue is that it is interpolating the data so it ends up like .. VALUES (.., 'NULL', ..) and not .. VALUES (.., NULL, ..) as it should be.
– user166390
                Aug 14, 2012 at 2:35
                Also, why does posNum use ’ (a funny single quote, which is wrong) while the others uses ' (apostrophe, which is correct)? Can you enter any value for posNum? (That is, is the title a red herring?)
– user166390
                Aug 14, 2012 at 2:40
                This is the classic '"" is not the same as NULL'. NULL is never equal to anything, and you can't substitute anything for it (well, I can't say never - Oracle has been an exception for years). If you want to assign NULL, assign NULL. If you want to see if a column is NULL, use IS NULL; they have IsNull() and Coalesce() specifically to deal with NULL values - that's why they have special functions and an operator to check for it. NULL <> anything else, and nothing else = NULL.
– Ken White
                Aug 14, 2012 at 2:44
                @pst: I'm pretty sure it is using '' not 'NULL' where it should be using just a literal NULL.
– mu is too short
                Aug 14, 2012 at 2:47
                BTW, ASP using the classic SQL drivers DOES support prepared statements: stackoverflow.com/questions/8538979/….   Vince, please consider using prepared statements, for readability, for the sanity of all around you, and for Poor Bobby Tables xkcd.com/327
– JayC
                Aug 14, 2012 at 3:44
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.
                Thanks a lot for that Craig! Everything in that opened my mind, it's really appreciated. Also, I realize that my application is insecure, but it's still in development yet and no one really has access to it but me. It will also not be accessible through Internet since its users will come from Intranet pages of my Agency. Thanks again!
– Vince.CRA
                Aug 15, 2012 at 14:26
                @Vince.CRA IMO the best time to make it secure it as you write it, right from the start. Security is not an add-on after you're finished, you need to think about it in design, tool selection, right on to deployment if you want to get it vaguely right. As for not being on the Internet - sure the attack surface is lower, but one curious / disgruntled employee (or misconfigured proxy server) is all it takes. There are tools that make exploiting simple SQL injection flaws something anyone can do.
– Craig Ringer
                Aug 15, 2012 at 23:26
                @Vince.CRA Also, seriously, you're developing a new application in ASP classic? That's an ... interesting ... decision. How's that going to be to support?
– Craig Ringer
                Aug 15, 2012 at 23:27
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.