TL;DR: Please look at the example at the bottom of my post here instead.
I know that this was discussed but the technique of building SQL strings manually is known to be a very serious security issue, and I feel that it should not be an accepted answer to such a question, unless also paired with an example of why it is dangerous to do this or at least a safe version.
As an example of why this is dangerous, unless the driver is protecting against this sort of thing by blocking the execution of multiple statements in a single DB call (it very well may), the following value could easily cause “problems” for you if entered into TextField1 (depending on the exact way you construct the query, you may need to swap ’ for " in this example):
1'; DROP TABLE domande; SELECT * FROM domande WHERE testo LIKE '
If not handled correctly, this value injected into the query above would drop the domande table. Some drivers do protect against this but you cannot always rely on this.
Another example (again, type this into TextField1, and again perhaps swap ’ for "):
1' OR 1=1 OR '
This example should cause all items in the table to be returned even if they do NOT match the LIKE clause. This is a serious issue for security reasons, typically. A local application may not very much care of course. This example cannot be blocked by a driver as it is a single statement still.
The least harmful but most likely example is simply:
'Hello there' he said
This example would simply cause a syntax error. The quotation characters ’ or " could never be safely used in a value provided by a user for use in such a query, as they would cause syntax errors. This example also cannot be blocked by a driver as it remains a single statement.
In an effort to complete the picture here, I have provided a version using prepared statements below. I believe this is not actually any harder to understand and I guarantee that it is much safer.
Dim t1 As String
Dim fab As RecordSet
// Create the "template" for our SQL query -- each ? will be safely bound to a type and value below
Dim ps As PreparedSQLStatement = db.Prepare("SELECT * FROM domande WHERE testo LIKE ?")
// Tell the statement that we expect a string to be used for the first parameter (number 0)
// Add our value from the Xojo variable, with a wildcard added to the end (for the first
// parameter, number 0 again)
// If you have more than one parmaeter (e.g. more than one ? in the SQL above) then continue
// binding types and values to parameters 1, 2, 3, etc.
// Get the results, returns a RecordSet exactly like calling db.SQLSelect would
fab = ps.SQLSelect
If fab <> Nil Then
While Not fab.EOF
ListBox2.Cell(Listbox2.LastIndex,0) = fab.Field("testo").StringValue
If db.Error Then MsgBox(db.ErrorMessage)
I hope that this example helps someone write safer code, we need to look out for each other out there.