Selecting record(s) from one or more user inputs

General
1

I want to select one or more records from my SQLite database based on user input with one or more partial criteria (Firstname, lastname, email, phone, or ID). I have a form with blank fields for each of the values that the user can enter part or all of any field (LIKE %value% comparisons). What is the best API 2.0 way to select records that match all of whatever values entered avoiding SQL injection?

2

Use the Recordset object

Var Recordset as RowSet = Session.DBlink.SelectSQL(cSQL_Consulta)

You can see this in the documentation:

3

I appreciate the suggestion of using the RowSet, but I am struggling with the construction of the SQL query.

4

First things first, don’t try and concatenate the input values to form an sql command. That will open you up to sql injection.

You are safe to build the skeleton of the SQL select statement. You can use ? As placeholders for the data. For example:

Var cSQL as String
Var lDone as Boolean = false
Var aParams() as Variant
cSQL = “select fieldlist from MyTable where “
If FirstName.Text <> “” then
      cSQL = cSQL + “FieldName like ?”
      lDone = True
      aParams.Add( “%” + FirstName.text + “%” )
End IF
If SurName.text <> “” then
      If lDone then cSQL = cSQL + “ and “
      cSQL = cSQL + “Surname like ?”
      lDone = true
      aParams.Add( “%” + Surname.text + “%” )
End IF
…etc…
Results = MyDB.SelectSQL( cSQL, aParams )

The %s must go in the params, not in the SQL string. ? replaces a whole parameter and can’t be mixed with other things. So “column like ?” Is valid, “column like %?%” is not.

Don’t worry about escaping, it will do it for you. You don’t need “s around you strings, for example. You don’t need to worry about names like O’Grady. Etc

Sorry about the smart quotes, I’m on a phone.

1 Like
5

Excellent - I hadn’t thought about adding one term at a time or building a parameter string.

Thank you!

6

No problem. The nice thing is you can look at the string you’ve built and ensure it’s well formed before execution.

Obviously, you can choose And / Or between fields in the if lDone part.

7

Fully implemented and it works like a charm! Thanks.

1 Like