Select with where clause

Databases
1

Hi,

The where clause is not working with strings for me.
Below is my code:

If DB.databaseFile.exists = True then if DB.Connect() = true then rs=db.SQLSelect("select NOM, VILLE, ID from CLIENTS WHERE NUMERO="+EditField1.Text) if rs = NIL then MsgBox(db.errormessage) return else lblisting.AddRow(rs.IdxField(1).StringValue) lblisting.Cell(lblisting.LastIndex,1)=rs.IdxField(2).StringValue lblisting.Cell(lblisting.LastIndex,2)=rs.IdxField(3).StringValue end rs.Close db.Close else MsgBox("Load "+db.ErrorMessage) end END
I am trying to retrieve datas from the table below:

db.SQLExecute( "create table CLIENTS (NUMERO VARCHAR, NOM varchar, ADRESSE VARCHAR, CODEPOSTAL varchar, VILLE VARCHAR, PAYS VARCHAR, CONTACT VARCHAR,TELEPHONE varchar, ID integer NOT NULL PRIMARY KEY)")

The only time its’ working is when I use “CODEPOSTAL” or “ID”. “CODEPOSTAL” is a zip code, a number but it’s registered as a varchar in my database
When I use “NUMERO” or “NOM” , which are both strings I get an error : "no such column: " + the “NUMERO” or “NOM” I have just entered.
Any idea to solve this strange behavior.

Thanks

Chris

2

You need to quote your value with single quotes. Like this:
WHERE NUMERO=’"+EditField1.Text+"’".

Anyway, this approach should be replaced by using prepared statements to prevent injection attacks and quoting issues.

3

When you execute that SQL statement, you’re not sending the EditField1 control itself, you’re sending the contents of the control, which is a text string. You have to enclose text strings in single quotes. So your SQLSelect line should be:

rs=db.SQLSelect("select NOM, VILLE, ID from CLIENTS WHERE NUMERO='"+EditField1.Text+"'")

The reason it works without single quotes on CODEPOSTAL and ID is because the “string” consisted only of numbers, which SQLite is able to “auto-convert” to a string. But anything with alpha characters it will try to interpret as a column, function or keyword and not auto-convert it. Just like Xojo would do in your code if you didn’t enclose text strings in double quotes.

4

Thanks to both of you, it’s now working.
I have a question for Maximilian :
What do you mean by “using prepared statements to prevent injection attacks and quoting issues.”
Should I change the code to do the same job?
Do you have an example?

Thanks again

Chris

5

http://developer.xojo.com/userguide/sql-injection

(sorry for the lmgtfy-link I posted earlier, I always find these condescending when I see them, I’m surprised I’m prone to use these myself)

There is also an example project in the Xojo/Examples/database/preparedSQLStatement folder.

6

I like to create my queries in a GUI tool like Microsoft SQL Server Management Studio, then I view the SQL format and change it to dynamic code.