empty values in INSERT INTO statement

I have this ASP page that inserts new records into an Access database via an
execute statement.

Taking the data from a form, I build my SQL statement like this:
<%
strSQL = "INSERT INTO mytable (Firstname, Lastname, Department, Extension)
_
& " VALUES ('" & CStr(Replace(Request.Form("Firstname"), "'", "''")) _
& "', '" & CStr(Replace(Request.Form("Lastname"), "'", "''")) _
& "', " & CInt(Replace(Request.Form("Department"), "'", "''")) _
& ", " & CInt(Replace(Request.Form("Extension"), "'", "''")) & ")"
%>

That works ok as long as ALL the fields in the form contain data. If one of
the number fields (Department and Extension) are empty, and error is
returned.

I guess it has something to do with the fact that text values are surrounded
by delimiting apostrophies while number fields are not.

So with complete data the above lines generate the following SQL statement:

INSERT INTO mytable (Firstname, Lastname, Department, Extension) VALUES
('John', 'Doe', 3, 55)

If the texts fields are empty then the statement would be:

INSERT INTO mytable (Firstname, Lastname, Department, Extension) VALUES ('',
'', 3, 55)

which is also OK.

But if the number fields are emtpy the statement become:

INSERT INTO mytable (Firstname, Lastname, Department, Extension) VALUES
('John', 'Doe', , )

and that produces an error.

How can I change the above SQL building code so as to avoid this kind of
problem? For example if I could make the word NULL appear instead of
(nothing) then SQL statement would work

Any suggestions would be highly appreciated.

Thanks

TB

Re: empty values in INSERT INTO statement

TB wrote:
> I have this ASP page that inserts new records into an Access database
> via an execute statement.
>
> Taking the data from a form, I build my SQL statement like this:
> <%
> strSQL = "INSERT INTO mytable (Firstname, Lastname, Department,
> Extension) _
> & " VALUES ('" & CStr(Replace(Request.Form("Firstname"), "'",
> "''")) _ & "', '" & CStr(Replace(Request.Form("Lastname"), "'",
> "''")) _ & "', " & CInt(Replace(Request.Form("Department"), "'",
> "''")) _ & ", " & CInt(Replace(Request.Form("Extension"), "'",
> "''")) & ")" %>
>
> That works ok as long as ALL the fields in the form contain data. If
> one of the number fields (Department and Extension) are empty, and
> error is returned.
>
> I guess it has something to do with the fact that text values are
> surrounded by delimiting apostrophies while number fields are not.
>
> So with complete data the above lines generate the following SQL
> statement:
> INSERT INTO mytable (Firstname, Lastname, Department, Extension)
> VALUES ('John', 'Doe', 3, 55)
>
> If the texts fields are empty then the statement would be:
>
> INSERT INTO mytable (Firstname, Lastname, Department, Extension)
> VALUES ('', '', 3, 55)
>
> which is also OK.
>
> But if the number fields are emtpy the statement become:
>
> INSERT INTO mytable (Firstname, Lastname, Department, Extension)
> VALUES ('John', 'Doe', , )
>
> and that produces an error.
>
> How can I change the above SQL building code so as to avoid this kind
> of problem? For example if I could make the word NULL appear instead
> of (nothing) then SQL statement would work
>
> Any suggestions would be highly appreciated.
>
> Thanks
>
As usual, your problem stems from using dynamic sql (do I sound like a
broken record rere? )


If you want to stick with dynamic sql, you will need to substitute the word
"null" for your request variables when they are empty. That way, the
resulting sql looks like this:

.... VALUES ('John', 'Doe', null, null )


With parameters, the problem is simpler. Post back if you wish to hear more.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: empty values in INSERT INTO statement

Of course I want to hear more about parameters, if you don't mind.

And yes, SQL is one the more commonly words spoken by me. I find it a very
interesting dicipline which properly harnessed with other (for me) pending
issues like Getrows() gives fantastic results.

TB

"Bob Barrows [MVP]" wrote in message
news:OvNsup5RFHA.3088@TK2MSFTNGP15.phx.gbl...
> TB wrote:
>> I have this ASP page that inserts new records into an Access database
>> via an execute statement.
>>
>> Taking the data from a form, I build my SQL statement like this:
>> <%
>> strSQL = "INSERT INTO mytable (Firstname, Lastname, Department,
>> Extension) _
>> & " VALUES ('" & CStr(Replace(Request.Form("Firstname"), "'",
>> "''")) _ & "', '" & CStr(Replace(Request.Form("Lastname"), "'",
>> "''")) _ & "', " & CInt(Replace(Request.Form("Department"), "'",
>> "''")) _ & ", " & CInt(Replace(Request.Form("Extension"), "'",
>> "''")) & ")" %>
>>
>> That works ok as long as ALL the fields in the form contain data. If
>> one of the number fields (Department and Extension) are empty, and
>> error is returned.
>>
>> I guess it has something to do with the fact that text values are
>> surrounded by delimiting apostrophies while number fields are not.
>>
>> So with complete data the above lines generate the following SQL
>> statement:
>> INSERT INTO mytable (Firstname, Lastname, Department, Extension)
>> VALUES ('John', 'Doe', 3, 55)
>>
>> If the texts fields are empty then the statement would be:
>>
>> INSERT INTO mytable (Firstname, Lastname, Department, Extension)
>> VALUES ('', '', 3, 55)
>>
>> which is also OK.
>>
>> But if the number fields are emtpy the statement become:
>>
>> INSERT INTO mytable (Firstname, Lastname, Department, Extension)
>> VALUES ('John', 'Doe', , )
>>
>> and that produces an error.
>>
>> How can I change the above SQL building code so as to avoid this kind
>> of problem? For example if I could make the word NULL appear instead
>> of (nothing) then SQL statement would work
>>
>> Any suggestions would be highly appreciated.
>>
>> Thanks
>>
> As usual, your problem stems from using dynamic sql (do I sound like a
> broken record rere? )
>
>
> If you want to stick with dynamic sql, you will need to substitute the
> word "null" for your request variables when they are empty. That way, the
> resulting sql looks like this:
>
> ... VALUES ('John', 'Doe', null, null )
>
>
> With parameters, the problem is simpler. Post back if you wish to hear
> more.
>
> Bob Barrows
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: empty values in INSERT INTO statement

TB wrote:
> Of course I want to hear more about parameters, if you don't mind.
>

OK, the short version:

There are two ways to use parameters: saved parameter queries (stored
procedures) and parameter markers with a Command object.

My recommendation is to use saved parameter queries:

Open your database in Access and create a saved query with this sql:

INSERT INTO mytable (Firstname, Lastname, Department, Extension)
VALUES ([p1],[p2],[p3],[p4])

Test it and verify that it works. Access will prompt you for values for the
parameters (p1, ...)
When you are satisfied that it works, save it with the name qInsRecord.
Close your database. In ASP:

<%
dim cn, sFirst, sLast, iDept, iExt, key, s

'Regardless of which option you are using start by validating inputs

for each key in Request.Form
s=lcase(request.form(key))
'The following should be down with regexp. This is a demo
if instr(s,"select")>0 OR instr(s,"insert")>0 OR instr(s,"update")>0 _
OR instr(s,"delete")>0 THEN
'punish the hacker
response.redirect "long_loading_page.htm"
end if
next

sFirst = cstr(Request.Form("Firstname"))
sFirst = cstr(Request.Form("Lastname"))
iDept = Request.Form("Department")
if len(iDept) = 0 then
iDept = null
else
on error resume next
iDept = cint(iDept)
if err<>0 then response.redirect "invalid_entry.htm"
on error goto 0
end if

iExt = Request.Form("Extension")
if len(iExt) = 0 then
iExt= null
else
on error resume next
iExt= cint(iExt)
if err<>0 then response.redirect "invalid_entry.htm"
on error goto 0
end if

'run the query
set cn=createobject("adodb.connection")
cn.open ""
cn.qInsRecord sFirst,sLast,iDept,iExt
cn.close: set cn=nothing
%>

And that's it.

If for some reason you cannot use saved parameter queries, you can use
parameter markers instead:

<%
dim cn, cmd, sSQL,sFirst, sLast, iDept, iExt, key, s, arParms
'validate data as above, then

sSQL = "INSERT INTO mytable (Firstname, Lastname," & _
" Department, Extension) VALUES (?,?,?,?)"

arParms=array(sFirst,sLast,iDept,iExt)

set cn=createobject("adodb.connection")
cn.open ""

set cmd=createobject("adodb.command")
cmd.CommandText=sSQL
set cmd.ActiveConnection=cn
cmd.Execute ,arParms,129

set cmd=nothing
cn.close: set cn=nothing
%>

You can read more in these links:

Saved parameter queries:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

Using the Command object:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: empty values in INSERT INTO statement

Thanks for a very complete piece information (which you call short).

I will test it tomorrow, and let you know. Thanks.

TB

"Bob Barrows [MVP]" wrote in message
news:Oyn%23Dx$RFHA.1348@TK2MSFTNGP15.phx.gbl...
> TB wrote:
>> Of course I want to hear more about parameters, if you don't mind.
>>
>
> OK, the short version:
>
> There are two ways to use parameters: saved parameter queries (stored
> procedures) and parameter markers with a Command object.
>
> My recommendation is to use saved parameter queries:
>
> Open your database in Access and create a saved query with this sql:
>
> INSERT INTO mytable (Firstname, Lastname, Department, Extension)
> VALUES ([p1],[p2],[p3],[p4])
>
> Test it and verify that it works. Access will prompt you for values for
> the parameters (p1, ...)
> When you are satisfied that it works, save it with the name qInsRecord.
> Close your database. In ASP:
>
> <%
> dim cn, sFirst, sLast, iDept, iExt, key, s
>
> 'Regardless of which option you are using start by validating inputs
>
> for each key in Request.Form
> s=lcase(request.form(key))
> 'The following should be down with regexp. This is a demo
> if instr(s,"select")>0 OR instr(s,"insert")>0 OR instr(s,"update")>0 _
> OR instr(s,"delete")>0 THEN
> 'punish the hacker
> response.redirect "long_loading_page.htm"
> end if
> next
>
> sFirst = cstr(Request.Form("Firstname"))
> sFirst = cstr(Request.Form("Lastname"))
> iDept = Request.Form("Department")
> if len(iDept) = 0 then
> iDept = null
> else
> on error resume next
> iDept = cint(iDept)
> if err<>0 then response.redirect "invalid_entry.htm"
> on error goto 0
> end if
>
> iExt = Request.Form("Extension")
> if len(iExt) = 0 then
> iExt= null
> else
> on error resume next
> iExt= cint(iExt)
> if err<>0 then response.redirect "invalid_entry.htm"
> on error goto 0
> end if
>
> 'run the query
> set cn=createobject("adodb.connection")
> cn.open ""
> cn.qInsRecord sFirst,sLast,iDept,iExt
> cn.close: set cn=nothing
> %>
>
> And that's it.
>
> If for some reason you cannot use saved parameter queries, you can use
> parameter markers instead:
>
> <%
> dim cn, cmd, sSQL,sFirst, sLast, iDept, iExt, key, s, arParms
> 'validate data as above, then
>
> sSQL = "INSERT INTO mytable (Firstname, Lastname," & _
> " Department, Extension) VALUES (?,?,?,?)"
>
> arParms=array(sFirst,sLast,iDept,iExt)
>
> set cn=createobject("adodb.connection")
> cn.open ""
>
> set cmd=createobject("adodb.command")
> cmd.CommandText=sSQL
> set cmd.ActiveConnection=cn
> cmd.Execute ,arParms,129
>
> set cmd=nothing
> cn.close: set cn=nothing
> %>
>
> You can read more in these links:
>
> Saved parameter queries:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> Using the Command object:
> http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: empty values in INSERT INTO statement

Bob, I'm not trying to be overly negative, I know you post to these news
groups regularly and make a substantial contribution... But I felt I needed
to add something, and hope you won't take it badly.

"Bob Barrows [MVP]" wrote in message
news:Oyn%23Dx$RFHA.1348@TK2MSFTNGP15.phx.gbl...

[snip]
> 'Regardless of which option you are using start by validating inputs
>
> for each key in Request.Form
> s=lcase(request.form(key))
> 'The following should be down with regexp. This is a demo
> if instr(s,"select")>0 OR instr(s,"insert")>0 OR instr(s,"update")>0 _
> OR instr(s,"delete")>0 THEN
> 'punish the hacker
[snip]

If you're using parameters what's the point? Text inside a parameter will
not be executed as SQL, ever, plus those words could occur in valid input,
e.g., [Product Name] = 'Select Beef'. What's more, those InStr calls are
case-sensitive without some more [optional] arguments, while SQL is not, so
any variation of case would thwart this check.

What's even more, even without parameterizing those values, a SQL injection
would depend on at least some punctuation, such as a semi-colon or
double-dash (neither of which may work in Jet, don't know, don't care,
that's beside the point.) A SQL injection has to terminate the fragment of
the intended statement to the left of the injection, and preserve syntax in
spite of the fragment to the right of the injection (usually via the comment
character.) The whole statement, with injection has to be syntactically
valid, or the whole statement fails.

I'm not saying that input validation when using parameterized SQL is
unnecessary, but such needs are app-specific. Searching parameterized
values for possible SQL injection is pointless.

I'm also not inferring that this fragment was presented as a complete
validation solution, however, as examples of concept implementations go,
this one lacks critical functionality, and doesn't do much to illustrate the
underlying concept -- at minimum it should perform a case-insensitive test,
e,g.,

vbTextCompare = 1
[...]
if (InStr(1, s, "delete", vbTextCompare) > 0) Or ...

And ideally it should test for a more positive indication of attempted SQL
injection, because this level of test could easily return a false positive
for input that in actuality is valid as well as harmless.

Bottom line, any time you're thinking "punish the hacker," you're treading
on thin ice, and you'd best make damn sure there really is a hacker to
punish! And just as important [if not more so], if you have something
special planned for hackers, best make DAMN sure ALL of your security ducks
are in a row, because if you are unlucky enough to draw their attention,
you'll find out the hard way that they are relentless, and capable of
dishing out punishments of their own. It can be excruciating.


-Mark

Re: empty values in INSERT INTO statement

Mark J. McGinty wrote:
> Bob, I'm not trying to be overly negative, I know you post to these
> news groups regularly and make a substantial contribution... But I
> felt I needed to add something, and hope you won't take it badly.

Not at all. You've given me a chance to clarify what I posted.

>
> "Bob Barrows [MVP]" wrote in message
> news:Oyn%23Dx$RFHA.1348@TK2MSFTNGP15.phx.gbl...
>
> [snip]
>> 'Regardless of which option you are using start by validating inputs
>>
>> for each key in Request.Form
>> s=lcase(request.form(key))
>> 'The following should be down with regexp. This is a demo
>> if instr(s,"select")>0 OR instr(s,"insert")>0 OR
>> instr(s,"update")>0 _ OR instr(s,"delete")>0 THEN
>> 'punish the hacker
> [snip]
>
> If you're using parameters what's the point?

The point is that if you don't make it difficult for a hacker, he will
continue attempting different exploits until he finally finds your site's
vulnerability. If the only consequence of his bad behavior is a simple "not
authorized" message, he will realize that sql injection is not going to work
and start trying other exploits until he finds te one that works. However,
if the consequence of each of his attempts is a page that takes 5 min. to
load, (or better yet, a page that simply launches itself in new windows,
forcing him to kill the process with task manager) how many attempts do you
think he will make before he moves on to greener pastures?

> Text inside a parameter
> will not be executed as SQL, ever, plus those words could occur in
> valid input, e.g., [Product Name] = 'Select Beef'.

It was just a suggestion. These were obviously fields that contained the
names of people, making it unlikely that they would contain SQL keywords. Of
course, if the text could contain these words, you would use some other
scheme.

> What's more,
> those InStr calls are case-sensitive without some more [optional]
> arguments, while SQL is not, so any variation of case would thwart
> this check.

.... which is why I used the lcase function ... :-)

>
> What's even more, even without parameterizing those values, a SQL
> injection would depend on at least some punctuation, such as a
> semi-colon or double-dash (neither of which may work in Jet, don't
> know, don't care, that's beside the point.)

Not really. A union statement needs no punctuation.

However, that's not the point. The thing is, the hacker probably does not
know what backend you are using. He is likely a script kiddie running a
script that successfully broke into other sites. He is not likely to be
tailoring his attack to the backend database you are using.

>
> I'm not saying that input validation when using parameterized SQL is
> unnecessary, but such needs are app-specific. Searching parameterized
> values for possible SQL injection is pointless.

Agreed. When this is the only defense, it is certainly pointless.

I recently attended a presentation by Patrick Hynds and Duane LaFlotte, who
based their presentaion on a new book they are working on called "Hacker vs.
Hacker" (a takeoff on the Mad "Spy vs. Spy" series). One of their keys was
"punish obviously bad behavior". Not taunt: punish. To reiterate: the key
word is "obvious". The entry of a SQL keyword into a name field is obvious
bad behavior that needs to be punished.

Another key was "multi-layered defense". Look for the book when it comes
out. I plan to.


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"