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