Dealing with Zero Length Strings in an Update Command

[JohnnyPolite]

Hello,

I am using multiple update commands on my site and I am not sure how to deal with zero length strings. I am sure you check the length, but I can't sort it out in my head how to do it. I have many fields in each update command so I was thinking I could set up a function.

To be honest, I am very new at ASP VBScript. I am halfway decent at VBA, but for some reason I can't wrap my head around this. I will post one of my update commands to give you an idea of how many fields are in one command.

"UPDATE tblStudentContacts SET MomFstName = '" & Request.Form("MomFstName") & "', MomLstName = '" & Request.Form("MomLstName") & _
"', MomGuard = '" & Request.Form("grpMomGuard") & "', MomAdd = '" & Request.Form("MomAdd") & "', MomAdd2 = '" & Request.Form("MomAdd2") & "', MomCity = '" & Request.Form("MomCity") & "'," & _
" MomSt = '" & Request.Form("MomSt") & "', MomZip = '" & Request.Form("MomZip") & "', MomHomePh = '" & Request.Form("MomHomePh") & "', " & _
"MomWorkPh = '" & Request.Form("MomWorkPh") & "', MomCellPh = '" & Request.Form("MomCellPh") & "', MomEmail = '" & Request.Form("MomEmail") & "', " & _
"DadFstName = '" & Request.Form("DadFstName") & "', DadLstName = '" & Request.Form("DadLstName") & "', DadGuard = '" & Request.Form("grpDadGuard") & "', " & _
"DadAdd = '" & Request.Form("DadAdd") & "', DadAdd2 = '" & Request.Form("DadAdd2") & "', DadCity = '" & Request.Form("DadCity") & "', DadSt = '" & Request.Form("DadSt") & "', " & _
"DadZip = '" & Request.Form("DadZip") & "', DadHomePh = '" & Request.Form("DadHomePh") & "', DadWorkPh = '" & Request.Form("DadWorkPh") & "', " & _
"DadCellPh = '" & Request.Form("DadCellPh") & "', DadEmail = '" & Request.Form("DadEmail") & "' WHERE StuID = " & Request.Form("StuID")

If there is a more efficient way to write this command, I wouldn't be sad if someone helped me with that as well. StuID is a hidden field that I am passing from page to page as the index.

I appreciate any help. Thanks in advance!

-JP

 

[Spork52]

First off, user input should be validated, not inserted directly into your SQL statement. Read up on (very scary) SQL injection and ways to avoid it.

That said, below is a function that will grab form all fields (except submit) and create an update statement which will set blank fields to NULL.

<%
Function CreateUpdateSQLnull(TableName, FieldID, ValueID)

Dim SQL
Dim Table_Name
Dim Field_ID
SQL = ""
Table_Name = TableName
Field_ID = FieldID
Value_ID = ValueID

SQL = "UPDATE " + Table_Name + " SET "

For Each Field In Request.Form
If InStr(1,Field,"submit",1) = 0 then
if Request.Form(Field) <> "" then
SQL = SQL + Field + " = '" + Replace(Request.Form(Field), "'", "''") + "',"
else
SQL = SQL + Field + " = NULL,"
end if
End If
Next

'Get rid of last comma
SQL = Left(SQL,Len(SQL)-1)

SQL = SQL + " WHERE " + Field_ID + " = '" + Replace(Value_ID, "'", "''") + "'"

CreateUpdateSQLnull = SQL

End Function
%>

 

[Spork52]

P.S. Without extensive validation, including some way of preventing the submission of bogus form fields, the function I suggested is probably horribly insecure. It's the kind of thing that looks good, but could be a disaster.

 

[damber]

A few things:

1. You should really normalise your table. There is no need to have "MomFstName" and "DadFstName" - "first_name" would do just fine. Then you can have a "relation_type" column - which would allow you to have mum, dad, brother, sister, aunty, uncle, gramps, gran, friend, bookie, parole officer, etc, without creating even more unused columns.

2. If I enter this first name in your web form:
"'; DROP tblStudentContacts; --"
Your data would likely disappear... search google on SQL injection for more information, probing techniques, methods of resolution etc. (these include using stored procedures, parameterised queries, or just escaping the escape characters through replace() etc)

3. to check for zero length of a string just use len(sVariableName & "") - the (& "") bit concatenates an empty string in case the value is null (making it an empty string either way, or leaving the original string entact), which would normally return an error. You may want to allow some of these through if the database allows empty strings for some fields. Others you may want to tell the user there is an error

4. For numbers in the query, you can't just concatenate a non existent number (e.g. empty string) to the query (e.g. StuID) as there is no '' around the value - so it would end up like this: "UPDATE....... WHERE StuID = " which is missing the where value - in this case you need to check for the mandatory elements and tell the user when these are missing, or conditionally include the clause / field /etc in the query based on whether the value is present.

Of course, with a parameterised query or stored proc using the command object with parameters will solve multiple issues here.



=======================================
LessThanDot - The IT Community of the 21st Century

A smile is worth a thousand kind words. So smile, it's easy!

 

[JohnnyPolite]

Hey,

Thanks both of you. It is late and I do not have time to look into this stuff today, but I really appreciate the advice. I am continuing to learn this stuff on my own and I am so grateful for people like you and these forums.

I look forward to trying this stuff out and looking into SQL injection. That's some scary stuff.

Okay...time to sleep. Thanks again!

 

[JohnnyPolite]

Hello again,

I have been looking through the SQL Injection articles for the last couple hours. I am using MS Access to store my tables. I have read in multiple different places that it is harder to use SQL injection with Access. I am about to toy around with using a stored command to run through the information on my form and update it.

I have a cheat sheet that lists different commands used for SQL injection. I am thinking of creating a function containing an array and checking my input information against that array to see if there are any SQL commands being inserted.

Do you think that is a step in the right direction.

Again, thanks in advance?

-JP

 

[JohnnyPolite]

Whoops...a misplaced question mark. There is no question that I am thanking you. The question was do you think this is a step in the right direction.

 

[gbaughma]

Something like this helps:

DadFstName = Request.Form("DadFstName")
DadFstName = Replace(DadFstName,"'","''") ' Strip out single quotes

... and so forth

SQLStmt = "INSERT INTO table...."
Connect.Execute(SQLStmt)

Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com

 

[jfdabiri]

johnypolite,
is there anyway you can share that cheat sheet? because i am trying to validate my user input too. if you care to look, i have a thread on javascript section on this site dealing with sql injection.
thanks.