SQLIte Prepared Statements

Hi all,

I am currently working on a small desktop app which uses an SQLite database.

I understand it is good practise to use prepared statements to reduce the risk of SQL injection attacks, but are they needed in the case of a simple SQLSelect statement used to retrieve the contents of a lookup table which is used to populate a combobox ? There are no user supplied parameters in the SQLSelect statement, which is hard coded into the app.

Thanks.

If you control all the inputs, there is no need for a prepared statement.

Thankyou Kem. I suspected it would have been overkill for loading my lookup tables.

I actually like prepared statements. In fact, I think it is easier to get data in my SQL queries.

I made some kind of SQLite framework, a long time ago. I still use it.

  • I add a class with properties.
  • I “register” this class to the SQLite database using my framework. This framework uses introspection to look for public properties and the property types. ( the name of the class is the name of the table in the database )
  • It then checks if the properties exist in the table as fields, and add them if needed.
  • In my framework, I can add relationships… that will delete related records if a related “parent” record is deleted (using triggers).
    Works like a charm. Setting up a database became a no-brainer to me.

And under the hood, inserting data and stuff uses prepared statements.
I use it even for populating ComboBoxes and stuff like that. (Suddenly, a ComboBox sounds like a great product at KFC)

I just use an extention function “db.SelectSQL” with the SQL query, including the questionmarks. This function has a ParamArray to feed all the values as variants. Again… made my life easier…

I’m actually now using a prepared statement in part of my program since its the only way I can see to pass the contents of a property as an SQL query parameter.

Are there any dangers to this ?

No.

Thanks again Kem.

Finally, is there a way/need to remove/destroy the contents of a PreparedStatement after use ? I’m thinking similar to how we close a recordset after use.

No, by using prepared statements you avoid SQL injection. I always prefer prepared statements over anything else.

Plus: it’s easier to pass a variable into your query this way

Any chance you’d be willing to share this Framework? Or… have you considered putting it up for sale?

(Sounds like something Xojo might want to add to the IDE. )

[quote=391777:@paul townsend]Any chance you’d be willing to share this Framework? Or… have you considered putting it up for sale?

(Sounds like something Xojo might want to add to the IDE. )[/quote]

I haven’t thought about sharing or selling.
It is actually not too hard. If I have time, I could add some documentation around it. For me, it is pretty straightforward. But are just a few lines of code to get things started. PM me, I’ll figure things out.

And I don’t think Xojo has interest in my approach or putting it in the IDE at all…

[quote=391759:@Edwin van den Akker]I actually like prepared statements. In fact, I think it is easier to get data in my SQL queries.

I made some kind of SQLite framework, a long time ago. I still use it.

  • I add a class with properties.
  • I “register” this class to the SQLite database using my framework. This framework uses introspection to look for public properties and the property types. ( the name of the class is the name of the table in the database )
  • It then checks if the properties exist in the table as fields, and add them if needed.
  • In my framework, I can add relationships… that will delete related records if a related “parent” record is deleted (using triggers).
    Works like a charm. Setting up a database became a no-brainer to me.

And under the hood, inserting data and stuff uses prepared statements.
I use it even for populating ComboBoxes and stuff like that. (Suddenly, a ComboBox sounds like a great product at KFC)

I just use an extention function “db.SelectSQL” with the SQL query, including the questionmarks. This function has a ParamArray to feed all the values as variants. Again… made my life easier…[/quote]

what database are you using??

Sql injection is a serious threat in a web environment.

However, for a single user solution or a lan based local solution, sql injection is not an issue. An attacker would need to gain access to a local computer to be able to perform ‚sql injection‘, but if this is happening sql injection is the least of your problems. And it is unlikely that an attacker would bother about injecting sql, once he has gained local access. He will directly delete or copy or alter your sensitive data. Or walk away with your hardware.

If you think about changing the database underneath your solution later on, then this will be easier to be achieved when you stick with standard SQL and avoid prepared statements.

[quote=391832:@Oliver Osswald]Sql injection is a serious threat in a web environment.

However, for a single user solution or a lan based local solution, sql injection is not an issue. An attacker would need to gain access to a local computer to be able to perform ‚sql injection‘, but if this is happening sql injection is the least of your problems. And it is unlikely that an attacker would bother about injecting sql, once he has gained local access. He will directly delete or copy or alter your sensitive data. Or walk away with your hardware.

If you think about changing the database underneath your solution later on, then this will be easier to be achieved when you stick with standard SQL and avoid prepared statements.[/quote]

so according to you , if want to create web edition version of my application, i should used prepared statement for database access.

for a desktop application that is either single or multi user is not worth the trouble. Also i am using SQLite for single user and CubeSQL for multi user and the prepared statement for both is totally different.

[quote=391759:@Edwin van den Akker]I made some kind of SQLite framework, a long time ago. I still use it.
I add a class with properties.
I “register” this class to the SQLite database using my framework. This framework uses introspection to look for public properties and the property types. ( the name of the class is the name of the table in the database )
It then checks if the properties exist in the table as fields, and add them if needed.
In my framework, I can add relationships… that will delete related records if a related “parent” record is deleted (using triggers).
[/quote]

This sounds a lot like what ActiveRecord does. We do all the work through the classes rather than relying on triggers and relationships in the DB (although those are possible too).

ARGen, our ActiveRecord utility automatically generates the classes, and can generate an initial UI for you too. More info at https://www.bkeeney.com/allproducts/argen/

@Oliver Osswald it’s not just about an attack though. If you take any user input string, you don’t want your app crashing if the user uses single-quotes, and you shouldn’t clean that yourself.

For most, changing databases is theoretical only, but if it’s a true concern, you can always use a SQL builder that does the grunt work for you behind the scenes. And I just happen to have made one available. :slight_smile:

https://github.com/ktekinay/XOJO-SQLBuilder

Ok, if we go this way … :slight_smile: About a year ago I wrote a class connecting to either SQLite, MySQL, PostgresSql en MS-SQL in exactly the same way, uses prepared statements or plain sql under the hood, parameters like “$1, $27, S2” in random order and same syntax for the 4 different databases, no SQL in code, transnational execution of multiple statements, native xojo plugins, easy to use , output to temporary multi-table SQL database, database error-handling and -reporting etc.
Since one year I use this class in projects with it without any issue, I even forgot about how to setup prepared statements at all.

What I want to say is that it is worth the time to make or buy yourself something generic, it saves you lot’s of trouble in projects. And as we say: “many different ways lead to Rome”

And in addition to this class, I also created a set of data-aware controls for desktop for creating forms with minimum coding.

Yes

Yes. In this scenario I‘d rather not use prepared statements but plain sql. And I do not agree with Kem saying that one should not escape single quotes oneself. This is easily done and it works reliably.

But if one starts a new project and then build on top of his sql-builder or uses bkeeney‘s Argen, this would probably be a good investment for the future. It helps standardizing your database applications and make code more reusable.