Dilemna on how to Code what I need to do

[Guest]

Ok, I inherited some code written in vb that is part of a web application.
My overall objective is to be able to take multiple names from a "LastName"
text box and use those names in my SQL query against my database. Currently
the way it is coded, the text box will pass one name only to the next page,
which then gets formed into the SQL query. I will provide some examples of
the code that is used to perform this task...

Ok, when you put in a last name, it is assigned to the sLastname variable
with the following code:


Dim sLastname As String = Trim(Request.Form("txtLastname"))
If Len(sLastname) > 0 Then
sURL += "LastName=" & sLastname & "&"
End If
The html behind that form is as follows:
<td>Last Name</td>
<td><textarea style="font-family: Verdana, Arial, Helvetica,
sans-serif; color: #000000;" name="txtLastName" rows=3 cols=17
id="txtLastName"></textarea></td>
</tr>


After everything is evaluated, the corresponing URL is built with the
following code:

sURL = "Students.aspx?" & sURL
Response.Redirect(sURL)

So for an example, if I put in the last Name Washington, the final URL after
you hit the Search button would look like:
sURL "Students.aspx?LastName=Washington" String
The above line is a copy from the visual studio 2003 debugging mode
variable watch for the variable sURL

So now, we would jump over into students.aspx with the ?LastName=Washington
appendix

The first couple of lines in the Students.aspx.vb page are as follows:
Dim iQueryStringStart As Integer = Request.RawUrl.IndexOf("?")
Dim sQueryString As String = ""
If iQueryStringStart > 0 Then
sQueryString = Request.RawUrl.Substring(iQueryStringStart)
End If
If Request.Form.Count = 0 Then
Dim sNickName As String =
UCase(Trim(Request.QueryString("NickName")))
Dim sFirstName As String =
UCase(Trim(Request.QueryString("FirstName")))
Dim sLastname As String =
UCase(Trim(Request.QueryString("Lastname")))

then later on down the page

sBody = GetBody(sNickName, _
sFirstName, _
sLastname, _
sMatricDate, _
sSourceCode, _
sDegreeProgram, _
sCitizenship, _
sCity, _
sState, _
sCountry, _
sPhDAreaOfStudy, _
sApplComp, _
sApplResponse, _
sStudentStatus, _
sCondCalculus, _
sOrientationFee, _
sEmbarkApp, _
sSpecialAccept, _
sGAResident, _
sAsstRequested, _
sAsstOffered, _
sLocalCity, _
sLocalState, _
bPosted, _
sQueryString)

Then to jump into the GetBody function...
Function GetBody(ByVal sNickName As String, _
ByVal sFirstName As String, _
ByVal sLastname As String, _
ByVal sMatricDate As String, _
ByVal sSourceCode As String, _
ByVal sDegreeProgram As String, _
ByVal sCitizenship As String, _
ByVal sCity As String, _
ByVal sState As String, _
ByVal sCountry As String, _
ByVal sPhDAreaOfStudy As String, _
ByVal sApplComp As String, _
ByVal sApplResponse As String, _
ByVal sStudentStatus As String, _
ByVal sCondCalculus As String, _
ByVal sOrientationFee As String, _
ByVal sEmbarkApp As String, _
ByVal sSpecialAccept As String, _
ByVal sGAResident As String, _
ByVal sAsstRequested As String, _
ByVal sAsstOffered As String, _
ByVal sLocalCity As String, _
ByVal sLocalState As String, _
ByVal bPosted As Boolean, _
ByVal sQueryString As String)

Dim sRetVal As String = ""

sRetVal += "<table cellpadding=2 cellspacing=0 border=0
width=""100%"">" & vbCr
sRetVal += "<tr>" & vbCr
sRetVal += "<td>" & vbCr
sRetVal += "<form name=frmStudents method=post
action=""Students.aspx" & sQueryString & """>" & vbCr
sRetVal += "<p style=""margin-left:5pt;"">" & vbCr
sRetVal += BTN_BACK & vbCr
sRetVal += "<input type=image name=btnPost alt=""Post""
src=""images/ico_post.gif"">" & vbCr
sRetVal += "<input type=image name=btnDelete alt=""Delete""
src=""images/ico_delete.gif"">" & vbCr
sRetVal += BTN_CANCEL & vbCr
sRetVal += "</p>" & vbCr

Try
Dim cn As OleDbConnection = OpenDatabase(OLEDB_PROVIDER &
gsConnectionString)

Dim sStudentSQLWC As String = GetStudentSQLWC(sNickName, _
sFirstName, _
sLastname, _
sMatricDate, _
sSourceCode, _
sDegreeProgram, _
sCitizenship, _
sCity, _
sState, _
sCountry, _
sPhDAreaOfStudy, _
sApplComp, _
sApplResponse, _
sStudentStatus, _
sCondCalculus, _
sOrientationFee, _
sEmbarkApp, _
sSpecialAccept, _
sGAResident, _
sAsstRequested, _
sAsstOffered, _
sLocalCity, _
sLocalState, _
bPosted)

Dim sSQL As String = ""
sSQL += "SELECT COUNT(*) AS iCnt "
sSQL += "FROM Students "
sSQL += sStudentSQLWC

Dim iCnt As Integer = GetSingleResult(cn, sSQL, "iCnt",
VariantType.Integer)

sSQL = ""
sSQL += "SELECT StudentId, "
sSQL += " Posted, "
sSQL += " LastName, "
sSQL += " FirstName, "
sSQL += " DegreeProgram, "
sSQL += " MatricDate, "
sSQL += " StudentStatus "
sSQL += "FROM Students "
sSQL += sStudentSQLWC
sSQL += "ORDER BY LastName, "
sSQL += " FirstName, "
sSQL += " StudentStatus"

and then to see the GetStudentSQLWC function...

Function GetStudentSQLWC(ByVal sNickName As String, _
ByVal sFirstName As String, _
ByVal sLastname As String, _
ByVal sMatricDate As String, _
ByVal sSourceCode As String, _
ByVal sDegreeProgram As String, _
ByVal sCitizenship As String, _
ByVal sCity As String, _
ByVal sState As String, _
ByVal sCountry As String, _
ByVal sPhDAreaOfStudy As String, _
ByVal sApplComp As String, _
ByVal sApplResponse As String, _
ByVal sStudentStatus As String, _
ByVal sCondCalculus As String, _
ByVal sOrientationFee As String, _
ByVal sEmbarkApp As String, _
ByVal sSpecialAccept As String, _
ByVal sGAResident As String, _
ByVal sAsstRequested As String, _
ByVal sAsstOffered As String, _
ByVal sLocalCity As String, _
ByVal sLocalState As String, _
ByVal bPosted As Boolean) As String

Dim sRetVal As String = ""

sRetVal += SQLWC("NickName", sNickName, VariantType.String)
sRetVal += SQLWC("FirstName", sFirstName, VariantType.String)
sRetVal += SQLWC("Lastname", sLastname, VariantType.String)
sRetVal += SQLWC("MatricDate", sMatricDate, VariantType.String)
sRetVal += SQLWC("SourceCode", sSourceCode, VariantType.String)
sRetVal += SQLWC("DegreeProgram", sDegreeProgram, VariantType.String)
sRetVal += SQLWC("Citizenship", sCitizenship, VariantType.String)
sRetVal += SQLWC("City", sCity, VariantType.String)
sRetVal += SQLWC("State", sState, VariantType.String)
sRetVal += SQLWC("Country", sCountry, VariantType.String)
sRetVal += SQLWC("PhDAreaOfStudy", sPhDAreaOfStudy,
VariantType.String)
sRetVal += SQLWC("ApplComp", sApplComp, VariantType.Integer)
sRetVal += SQLWC("ApplResponse", sApplResponse, VariantType.String)
sRetVal += SQLWC("StudentStatus", sStudentStatus, VariantType.String)
sRetVal += SQLWC("CondCalculus", sCondCalculus, VariantType.Integer)
sRetVal += SQLWC("OrientationFee", sOrientationFee,
VariantType.Integer)
sRetVal += SQLWC("EmbarkApp", sEmbarkApp, VariantType.Integer)
sRetVal += SQLWC("SpecialAccept", sSpecialAccept, VariantType.Integer)
sRetVal += SQLWC("GAResident", sGAResident, VariantType.Integer)
sRetVal += SQLWC("AsstRequested", sAsstRequested, VariantType.Integer)
sRetVal += SQLWC("AsstOffered", sAsstOffered, VariantType.Integer)
sRetVal += SQLWC("LocalCity", sLocalCity, VariantType.String)
sRetVal += SQLWC("LocalState", sLocalState, VariantType.String)
sRetVal += SQLWC("Posted", bPosted, VariantType.Boolean)

If Len(sRetVal) > 0 Then
'add the " WHERE " clause and trim the final "AND "
sRetVal = " WHERE " & Left(sRetVal, Len(sRetVal) - 4) & " "
End If

Return sRetVal

And the SQLWC function is as follows:
Shared Function SQLWC(ByVal sField As String, _
ByVal oVal As Object, _
ByVal oType As VariantType) As String

Dim sRet As String = ""

Select Case oType
Case vbString
'Add If statement that will evaluate Oval string for Yes and
No
If Len(oVal) > 0 Then
'Add If statement that will evaluate Oval string for Yes
and No and convert to 1 or 0
If oVal = "YES" Then
oType = VariantType.Integer
oVal = 1
sRet = " " & sField & " = " & oVal.ToString.Trim() &
" AND "
ElseIf oVal = "NO" Then
oType = VariantType.Integer
oVal = 0
sRet = " " & sField & " = " & oVal.ToString.Trim() &
" AND "
'Added this elseif to search for different country
fields in alumni search
ElseIf sField = "WorkCountry" Or sField = "LocalCountry"
Or sField = "HomeCountry" Then
sRet = " UPPER(" & sField & ") LIKE " & SQLSTR(oVal
& "%") & " OR "
Else
sRet = " UPPER(" & sField & ") LIKE " & SQLSTR(oVal
& "%") & " AND "
End If

End If
Case vbBoolean
If oVal Then
sRet = " " & sField & " = 1 AND "
End If
Case vbInteger
If IsNumeric(oVal) Then
sRet = " " & sField & " = " & oVal.ToString.Trim() &
" AND "
End If
Case vbDate
If IsDate(oVal) Then
sRet = " " & sField & " = " & SQLSTR(oVal) & " AND "
End If
End Select

Return sRet


So hopefully, that is enough of a peek into the Code. I need to figure out
a way, IF I CAN, to work with this code, so that when you enter multiple
lastnames in the Lastname box, it will pass that information as a NAME, or
NAMe into the SQL query that it will build.

I am thinking I need to change the initial box from a string character to
something else... any ideas?

 

[Guest]

I am thinking I need to change the initial box from a string character
to something else... any ideas?

Take a look at Parameterized Queries - your code looks like it's ripe for a
SQL injection attack.

 

[Guest]

Thanks Spam..
I will work on that a little later, but in the mean time, I have a deadline
to try and fix the current code to handle my qequests.. any ideas....

Maybe some kind of way, turning the text box entry into an array, but not
sure how to set a delineator to separate the different names..

 

[Guest]

I will work on that a little later, but in the mean time, I have a
deadline
to try and fix the current code to handle my qequests.. any ideas....

You should make it a priority ... because someone could "DELETE FROM
TABLE" pretty easily ;-)

Maybe some kind of way, turning the text box entry into an array, but
not sure how to set a delineator to separate the different names..

You'll need to dynamically prompt for additional textboxes, then pass each
name as an individual URL parameter to the receiving page.

If can modify the code even more, perhaps post an XML document to the
receiving site with all the usernames/password?

Lastly you could use a web service instead which would solve a lot of
problem

 

[Guest]

What do you mean by the receiving site?

The dynamic idea sounds appealing, however, if the ywant to do multiple
first names as well, i am not sure how scalable that will be, with dynamic
text boxes popping up. Do you have an example of how to code that.

If i did not mention, i am very much a NOVICE at this

And for now, we back up the database daily, so if it gets wiped out UNTIL I
can protect against the SQL injection attacks (Which is an entirely different
can of worms, as I am not even sure where to begin to protect against that),
then at least I have some offsite backup....

Thanks SpamCatcher..

Thanks