[Solved] Using params in SQLite to refer to tables and fields
lazarus / 63-Database

----------------------------------------------------------------------
heebiejeebies · 2022-04-30 23:11

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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   
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 · 2022-05-01 00:05

AFAIK, you can't provide table and field names as parameters, only
values can be provided as parameters.

----------------------------------------------------------------------
PierceNg · 2022-05-01 01:04

Quote from: heebiejeebies on May 01, 2022, 01:11:33 amCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery3.SQL.Text:=('UPDATE :TableParam SET :FieldParam
=:CompletedParam WHERE ID =:RowParam;');
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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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: [Select]stmt := db.prepare('pragma table_info(' + TableParam +
')') // String construction ok since we have verified that TableParam
is good
stmt.execute()
The output looks something like this:

Code: [Select]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
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 · 2022-05-01 01:11

Quote from: heebiejeebies on May 01, 2022, 01:11:33 amHere'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.

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 · 2022-05-01 20:17

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!

----------------------------------------------------------------------
Zvoni · 2022-05-02 06:10

And don't use "SELECT * FROM" in production code
It's a ticking time-bomb

----------------------------------------------------------------------
Zvoni · 2022-05-02 06:44

btw: There is a "hacky" Workaround.
Create a Table as Follows:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE "tbl_update" (        "TableName"     TEXT,        "FieldName" 
  TEXT,        "FieldParam"    TEXT,        "RowParam" TEXT,       
"SQLString" TEXT GENERATED ALWAYS AS ('UPDATE ' || "TableName" || '
SET ' || "FieldName" || '=' || "FieldParam" || ' WHERE ID=' ||
"RowParam") VIRTUAL);1) Execute TRUNCATE tbl_update (to empty out the
staging table)
2) INSERT your Parameters into it (here you can even use Parameters)
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query3.SQL.text:='INSERT INTO tbl_update ("TableName", "FieldName",
"FieldParam", "RowParam") VALUES (:TableParam,:FieldParam,:CompletedPa
ram,:RowParam)';Query3.ParamByName('TableParam').AsString:='SomeTableN
ame';Query3.ParamByName('FieldParam').AsString:=ProtocolElement;Query3
.ParamByName('CompletedParam').AsString:=':CompletedParam';
 //IMPORTANT: NOTE THE
COLONQuery3.ParamByName('RowParam').AsString:=':RowParam';
//IMPORTANT: NOTE THE COLONQuery3.ExecSQL;Transaction.Commit;3) Read
out the SQLString from the Table
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
SQLString FROM tbl_updateIn the example this returns as a STRING
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i UPDATE
SomeTableName SET SomeProtocolElement=:CompletedParam WHERE
ID=:RowParam4) Set the String as ... SQL.Text, assign the two
Paramaters and go

EDIT: Instead of reading out the SQLString and setting the params from
the Frontend-code you could even use an AFTER INSERT Trigger to
automate it, but then you have to take care of DataTypes (Is it Text,
is it Integer etc.)

----------------------------------------------------------------------
Zvoni · 2022-05-02 08:07

Quote from: PierceNg on May 01, 2022, 03:04:38 amTo avoid injection
attack, should still verify that TableParam and FieldParam are valid.
First, query the table sqlite_master for the table name:
*snip*
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:
*snip*

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.
Whatever for?
TSQLite3Connection inherits from TSQLConnection, and there you have
the Methods "GetTableNames" and "GetFieldNames" (though IIRC,
SQLite3Conn overrides the second one)
Both return a TStrings-List where it's easy to check if a Table or
column exists