Recent

Author Topic: [Solved] Using params in SQLite to refer to tables and fields  (Read 942 times)

heebiejeebies

  • Full Member
  • ***
  • Posts: 102
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][+][-]
  1.  
  2.   Form1.Userfiletransaction.Active:= True;
  3.   SQLQuery2.Close;
  4.   SQLQuery2.SQL.Text :=('SELECT * FROM HandOffs WHERE _rowid_ = :RowParam');
  5.  
  6.   SQLQuery2.Params.ParamByName('RowParam').Value := HandOffRow;
  7.    SQLQuery2.Open;
  8.  
  9.   ProtocolElement := SQLQuery2.Fieldbyname('Element').AsString;
  10.  
  11.   ShowMessage(ProtocolElement); // Works - shows the correct field name
  12.  
  13. SQLQuery3.SQL.Text:=('UPDATE :TableParam SET :FieldParam =:CompletedParam WHERE ID =:RowParam;');
  14.  
  15.   SQLQuery3.Params.ParamByName('RowParam').Value := HandOffRow;
  16.   SQLQuery3.Params.ParamByName('CompletedParam').Value := 'True';
  17.   SQLQuery3.Params.ParamByName('FieldParam').Value := ProtocolElement;
  18.   SQLQuery3.Params.ParamByName('TableParam').Value := 'Custom1';
  19.  
  20. SQLQuery3.ExecSQL;
  21. Form1.Userfiletransaction.Commit;
  22.  
  23.  

Any thoughts? Can this even be done?? Thanks!  :)
« Last Edit: May 01, 2022, 10:17:29 pm by heebiejeebies »
Ubuntu 18.04/Lazarus 2.0.10 FPC 3.2.0

dseligo

  • Hero Member
  • *****
  • Posts: 719
Re: Using params in SQLite to refer to tables and fields
« Reply #1 on: May 01, 2022, 02:05:30 am »
AFAIK, you can't provide table and field names as parameters, only values can be provided as parameters.

PierceNg

  • Full Member
  • ***
  • Posts: 199
Re: Using params in SQLite to refer to tables and fields
« Reply #2 on: May 01, 2022, 03:04:38 am »
Code: Pascal  [Select][+][-]
  1. 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][+][-]
  1. 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.
« Last Edit: May 01, 2022, 03:16:06 am by PierceNg »

PierceNg

  • Full Member
  • ***
  • Posts: 199
Re: Using params in SQLite to refer to tables and fields
« Reply #3 on: May 01, 2022, 03:11:54 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.

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

  • Full Member
  • ***
  • Posts: 102
Re: Using params in SQLite to refer to tables and fields
« Reply #4 on: May 01, 2022, 10:17:11 pm »
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!
Ubuntu 18.04/Lazarus 2.0.10 FPC 3.2.0

Zvoni

  • Hero Member
  • *****
  • Posts: 1343
Re: [Solved] Using params in SQLite to refer to tables and fields
« Reply #5 on: May 02, 2022, 08:10:40 am »
And don't use "SELECT * FROM" in production code
It's a ticking time-bomb
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 1343
Re: [Solved] Using params in SQLite to refer to tables and fields
« Reply #6 on: May 02, 2022, 08:44:50 am »
btw: There is a "hacky" Workaround.
Create a Table as Follows:
Code: SQL  [Select][+][-]
  1. CREATE TABLE "tbl_update" (
  2.         "TableName"     TEXT,
  3.         "FieldName"     TEXT,
  4.         "FieldParam"    TEXT,
  5.         "RowParam" TEXT,
  6.         "SQLString" TEXT GENERATED ALWAYS AS ('UPDATE ' || "TableName" || ' SET ' || "FieldName" || '=' || "FieldParam" || ' WHERE ID=' || "RowParam") VIRTUAL
  7. );
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][+][-]
  1. Query3.SQL.text:='INSERT INTO tbl_update ("TableName", "FieldName", "FieldParam", "RowParam") VALUES (:TableParam,:FieldParam,:CompletedParam,:RowParam)';
  2. Query3.ParamByName('TableParam').AsString:='SomeTableName';
  3. Query3.ParamByName('FieldParam').AsString:=ProtocolElement;
  4. Query3.ParamByName('CompletedParam').AsString:=':CompletedParam';  //IMPORTANT: NOTE THE COLON
  5. Query3.ParamByName('RowParam').AsString:=':RowParam'; //IMPORTANT: NOTE THE COLON
  6. Query3.ExecSQL;
  7. Transaction.Commit;
3) Read out the SQLString from the Table
Code: SQL  [Select][+][-]
  1. SELECT SQLString FROM tbl_update
In the example this returns as a STRING
Code: SQL  [Select][+][-]
  1. UPDATE SomeTableName SET SomeProtocolElement=:CompletedParam WHERE ID=:RowParam
4) 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.)
« Last Edit: May 02, 2022, 08:57:21 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 1343
Re: Using params in SQLite to refer to tables and fields
« Reply #7 on: May 02, 2022, 10:07:27 am »
To 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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018