SQL Select with date

The first query is working , the second returns an empty record set

rs= db.sqlselect(“Select * from Person WHERE Data>= ‘2016-10-19’ AND Data<=‘2016-10-22’”)
rs= db.sqlselect(“Select * from Person WHERE Data>=” +data1+" AND Data<="+data2)

data1 and data2 are strings fromt text input, any suggestion?

Thanks

You’re probably missing the single quotes.

True, thanks!

Just a little remark…
When you deal with text input, you probably want to use PreparedStatements. It makes querying a bit easier. And it prevents SQL injection.

Edwin:
are-you telling that one can give access to its users (or bad doers) to sql commands IF the project use PreparedStatements ?

No… but SQL Injection is “possible” depending on the syntax of the query and if it does NOT use PreparedStatments (which will help protect from injections)

[quote=294481:@Emile Schwarz]Edwin:
are-you telling that one can give access to its users (or bad doers) to sql commands IF the project use PreparedStatements ?[/quote]

No, PreparedStatments helps to preventing that from happening.

For SQLite you could do something like this:
(I do this without Xojo open, so don’t mind the Typos :wink: )

Dim sql as string = "Select * from Person WHERE Data >= ? AND Data <= ?"
Dim ps as SQLitePreparedStatement = db.Prepare( sql )
' db is a SQLite Database, you are connected with

ps.BindType 0, ps.SQLiteText
ps.Bind 0, Data1.SQLDateTime
ps.BindType 1, ps.SQLiteText
ps.Bind 1, Data2.SQLDateTime
' Data1 and Data2 are the two dates variables, of type Date, that are not nil.

Dim rs as RecordSet = ps.SQLselect

If db.error then
  MsgBox db.ErrorMessage
end if

This is just a tiny example, with probably some typos. I’m sure you know to work this thing out. Otherwise the community is always very helpful. But of course, there is plenty of documentation about this matter.

For other database types it works pretty much the same way.