Forum > Database
[Solved] Using params in SQLite to refer to tables and fields
Zvoni:
And don't use "SELECT * FROM" in production code
It's a ticking time-bomb
Zvoni:
btw: There is a "hacky" Workaround.
Create a Table as Follows:
--- 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";}};} ---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 [+][-]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";}};} ---Query3.SQL.text:='INSERT INTO tbl_update ("TableName", "FieldName", "FieldParam", "RowParam") VALUES (:TableParam,:FieldParam,:CompletedParam,:RowParam)';Query3.ParamByName('TableParam').AsString:='SomeTableName';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 [+][-]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 SQLString FROM tbl_updateIn the example this returns as a STRING
--- 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";}};} ---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:
--- Quote from: PierceNg on May 01, 2022, 03:04:38 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.
--- End quote ---
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
Navigation
[0] Message Index
[*] Previous page