0

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?

Community
  • 1
  • 1
baeuchle
  • 5
  • 2
  • `Replace` is to be avoid if there is a better way. Here, there is no better way. – CL. Jul 01 '15 at 17:18
  • Well, I guess I have to follow this advice. I hope this really prevents SQL injections (and if it does, why does everybody keep saying that it is evil? What are the drawbacks?). I must say I'm disappointed that C# doesn't offer an Escape function, citing the possibility to use parameters at every turn, leaving someone with my problem alone out in the wild. -1 for Microsoft here. – baeuchle Jul 06 '15 at 13:09

0 Answers0