Defining an SQL query from various input textboxes

Question

I wanted to know what would be considered "best practice" in terms of defining an SQL query based on input from various textboxes on a web page.

Per say, lets say if you have 8 input textboxes on a website, would you be needing to use a bunch of if statements to evaluate what SQL query that you would end up using?

ie.

var sql
var a =  document.getElementByID('tb1').value
var b =  document.getElementByID('tb2').value
var c =  document.getElementByID('tb3').value
var d =  document.getElementByID('tb4').value

if (a.length > 0 && b.length > 0 && c.length > 0 && d.length > 0) {

sql = "SELECT * FROM table WHERE [firstname] = '"+a+"' AND [middlename] = '"+b+"' AND [lastname] = '"+c"' AND [organization] = '"+d+"'"

}

ps. Im also using client-side Microsoft Jet, so no server or any sorts.

I think I would be inclined to put that in the the "worst practice" category :-) — Pointy, Nov 17 '12 at 17:06
hence why I am asking the experts since I am a newbie to this. How could you re-scape the above into something more scalable? — Jason Kelly, Nov 17 '12 at 17:10
Well I don't know much about Jet, and in particular I don't know if it supports the concept of "prepared statements". That's normally considered the right way to do things with other RDBMSs. — Pointy, Nov 17 '12 at 17:32
Are you considering to have the browser send a SQL string to the server? A malicious client can send stuff like "drop table t". Are you aware of that? — usr, Nov 17 '12 at 18:41

score 0 · Accepted Answer · answered Nov 17 '12 at 18:39

You can save queries and use them with Ms Access/Jet/ACE. A few notes using ADODB command object and recordset, but use which ever command object suits best.

Set rs = Server.CreateObject("ADODB.Recordset")
Set cmd = Server.CreateObject("ADODB.Command")

cmd.ActiveConnection = objConn
cmd.CommandType = adCmdStoredProc

cmd.CommandText = "MySavedQuery"
cmd.Parameters.Append cmd.CreateParameter("@FirstName", adVarWChar, adParamInput, 50, varA)
cmd.Parameters.Append cmd.CreateParameter("@MiddleName", adVarWChar, adParamInput, 50, varB)

rs.LockType = adLockOptimistic
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.Open cmd.Execute

Defining an SQL query from various input textboxes

1 Answers1