Concatenating Strings into a Prepared SQLite3 Query
-
I'm a EE by training and have very little experience with databases, so I hope this isn't a dumb question. I have a database (SQLITE3) with (of course) a bunch of tables and a bunch of fields. I want to use a base class that prepares generic queries for reading and writing to the database, then I have a number of child classes that fill in the blanks for individual tables. For example, I define a generic SELECT query:
#define GEN_SELECT_QUERY "SELECT :columns FROM :table WHERE id=:id"Then I use a QSqlQuery instance to prepare the query, bind values, and execute. The child classes set their own columns with :columns, and every table has a PK named "id." (There may be a better way to do this all. Like I said, DBs are not my bread and butter.)
I think all of that is going to work ok. My question is about my INSERT query:
#define GEN_INSERT_QUERY "INSERT INTO :table (:columns) VALUES (:values)"The :values will have to be set by the child classes, and they will have to be concatenated from various user inputs (strings and integers). I know that concatenating into a query (particularly from user input) is considered unsafe. But I think I will be ok here, because I'm concatenating into a string, and then binding that string to a prepared query. Am I wrong? Is there a better way to do this? Or am I totally off, and need to completely redesign my database (which is an option for me)?
TIA!
-
@scrand I would suggest another approach: provide an API in your base class which completely abstracts from any database and let your child classes use this API. That would mean that all these child classes do not have to care about SQL at all, they just use that API, pass needed parameters and get the results. Only the base class would know that a SQL database is used and it would construct all needed queries and insert the parameters.
-
@jsulm , Thank you! That sounds like a great solution, and much more elegant than what I'm trying to hack together. Unfortunately, I'm not quite sure how to do it. Does the API have separate prepared queries (and separate member functions) for each table? I'm a pretty quick study if I have a go-by, so if you could point me to an example of something similar (or even the right search terms), I could probably work it out. Thanks!
-
Hi and welcome to devnet,
The API itself will depend on what you are going to insert in that table.
Let's take a temperature sensor, you would have something like a storeTemperature method taking the value and in that method call the correct insert query. I don't think you should start by generalizing your queries too much. Have something clean and working before trying to generalize too much.
If you take a look at the example of the QtSql module, you'll see several prepared queries for dedicated tables. It make the code more readable and understandable.
-
@SGaist, Ok, I think I get where you're both going now. I should have a Database handler class with separate queries for reading and writing each table. No need to over-complicate it by trying to abstract the queries themselves. If I'm honest with myself, I'm probably trying to do that because SQL is like that cute girl in high school who intimidates you a little bit. You're a little afraid to talk to her, and if you do work up the courage, you want to get as much done in that one conversation as possible. :-)
