Forum > Database
[Solved] Using params in SQLite to refer to tables and fields
heebiejeebies:
Hi all,
I'm getting an unspecified syntax error at my 'UPDATE' statement below. Everything works perfectly when I replace the table and field params with specific tables and fields, but no go when using the params.
Here's the full code. SQLQuery2 loads various things from the HandOffs table, but so far I've only implemented the code to read the 'Element' field. There is also a field in that table that stores the name of the required table for the update statement, but as I haven't done it yet I've just made the table param refer to the 'Custom1' table. The ShowMessage box in the middle of the code is just a test to confirm that the field name is being read correctly and passed on to the ProtocolElement variable.
So in a nutshell, I'm loading table and field names from the HandOffs table, and then updating that table and field using SQLQuery3.
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- Form1.Userfiletransaction.Active:= True; SQLQuery2.Close; SQLQuery2.SQL.Text :=('SELECT * FROM HandOffs WHERE _rowid_ = :RowParam'); SQLQuery2.Params.ParamByName('RowParam').Value := HandOffRow; SQLQuery2.Open; ProtocolElement := SQLQuery2.Fieldbyname('Element').AsString; ShowMessage(ProtocolElement); // Works - shows the correct field name SQLQuery3.SQL.Text:=('UPDATE :TableParam SET :FieldParam =:CompletedParam WHERE ID =:RowParam;'); SQLQuery3.Params.ParamByName('RowParam').Value := HandOffRow; SQLQuery3.Params.ParamByName('CompletedParam').Value := 'True'; SQLQuery3.Params.ParamByName('FieldParam').Value := ProtocolElement; SQLQuery3.Params.ParamByName('TableParam').Value := 'Custom1'; SQLQuery3.ExecSQL;Form1.Userfiletransaction.Commit;
Any thoughts? Can this even be done?? Thanks! :)
dseligo:
AFAIK, you can't provide table and field names as parameters, only values can be provided as parameters.
PierceNg:
--- Quote from: heebiejeebies on May 01, 2022, 01:11:33 am ---
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SQLQuery3.SQL.Text:=('UPDATE :TableParam SET :FieldParam =:CompletedParam WHERE ID =:RowParam;');
--- End quote ---
In SQLite, table and column names cannot be parameterized in this manner, so you'll have to use string construction.
To avoid injection attack, should still verify that TableParam and FieldParam are valid. First, query the table sqlite_master for the table name:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT tbl_name FROM sqlite_master WHERE TYPE = 'table' AND tbl_name = :TableParam
If the query is good, meaning the table named by TableParam exists, then check FieldParam via TableParam's metadata using the pragma table_info(). Pseudo code below, as I don't have any handy Pascal code demonstrating the same:
--- Code: ---stmt := db.prepare('pragma table_info(' + TableParam + ')') // String construction ok since we have verified that TableParam is good
stmt.execute()
--- End code ---
The output looks something like this:
--- Code: ---cid name type notnull dflt_value pk
--- ---------- -------- ------- ---------- --
0 id integer 1 1
1 key varchar 1 0
2 title varchar 1 0
3 country_id integer 0 0
4 club boolean 1 'f' 0
5 created_at datetime 1 0
6 updated_at datetime 1 0
--- End code ---
Programmatically, the pragma's output is itself an SQLite 'result set', and your application code then verifies the 'name' and 'type' columns for FieldParam using said output.
After determining that the column FieldParam exists and is of the expected type, then you construct your query string.
PierceNg:
--- Quote from: heebiejeebies on May 01, 2022, 01:11:33 am ---Here's the full code. SQLQuery2 loads various things from the HandOffs table, but so far I've only implemented the code to read the 'Element' field. There is also a field in that table that stores the name of the required table for the update statement, but as I haven't done it yet I've just made the table param refer to the 'Custom1' table. The ShowMessage box in the middle of the code is just a test to confirm that the field name is being read correctly and passed on to the ProtocolElement variable.
So in a nutshell, I'm loading table and field names from the HandOffs table, and then updating that table and field using SQLQuery3.
--- End quote ---
In SQL, table names, column names, column types, etc. are metadata. It is usually good practice to let the DBMS handle metadata. Processing metadata in application code routinely, as if it is user data, could be considered a design smell.
Any particular strong reasons to design your schema this way?
heebiejeebies:
Thanks for the replies :)
I've got it working with string construction. Wasn't aware you could do that, thought you had to use parameters. Thanks for the tips re: injection attack too, PierceNg!
Navigation
[0] Message Index
[#] Next page