mercredi 1 juillet 2015

Escaping SQL in C#/sqlite or: how can I use parameters here?

I realize that handcrafting SQL statements is evil. I also realize that the spontaneous answer of everyone seeing this post will be "Use parameters". I have found several occurences of similar questions around here, but I can't find out how they apply to my specific problem.

I use System.Data.SQLite with C#. I want to dynamically create a VIEW, say listview_asdf which I then can access inside a library with simple SELECT * FROM listview_asdf statements. That library uses loads of automatically generated statements for accessing my view.

Problem is, at one point I need to create a view like this:

CREATE VIEW listview_asdf AS SELECT * FROM tbl_asdf WHERE id IN (1,2,3)

not knowing how many and which parameters I have in the IN() list. Actually, the list will be different each time I use it (I delete the views in their Dispose() methods). Creating the view does - as far as I understand the relevant google result - not allow to use parameters.

So, I need to take an arbitrary-length array of parameters (easy) and write the CREATE VIEW statement with an IN (...) clause. Here, I need to insert the parameter values. This is no problem in case of integers, as shown above, but for strings or other types (System.DateTime comes to mind) I need some kind of escaping.

Else, I need some way of getting around the necessity of escaping. I don't know of any solution to either except the ominous

sql = sql.Replace("'","''");

which, as User Aur Saraf points out here, is a sure way of losing my job (which I do like).

Any ideas for a way out?

Aucun commentaire:

Enregistrer un commentaire