Is it possible to parameterize table and column names in SQLite queries?

Generally things like column names (or table names) can not be parameterised - and the fact that there are different indices means that it will have to be a different plan internally. So you will have to use concatenation - but be careful to white-list the known column names to prevent sql injection:

    SQLiteCommand cmd = new SQLiteCommand(@"
    SELECT [ID],[email],[serializedata],[restrictions]
    FROM " + whiteListedUserTable + @"
    WHERE [" + whiteListedColumnName + @"] = @searchparam", SQLConnection);

    cmd.Parameters.Add(new SQLiteParameter("@searchparam"));
    ...
    Command.Parameters["@searchparam"].Value = searchdata;

You cannot use a query parameter in that fashion -- to indicate a column name. You can only use it to supply values.

Consider something like this instead:

    SQLiteCommand cmd = new SQLiteCommand(
    "SELECT [ID]" +
        ",[email]" +
        ",[serializedata]" +
        ",[restrictions]" +
    " FROM " + UserTable +
    " WHERE [" + search + "] = @searchparam", SQLConnection);

    cmd.Parameters.Add(new SQLiteParameter("@searchparam"));

If you control all of the input to this function and none if it can be supplied by someone other than you, this should be safe. But if search comes from an untrusted third party, be sure to make the appropriate security checks on the value.


Comments

  1. Giuliani

    • 2021/3/4

    You cannot use a query parameter in that fashion -- to indicate a column name. You can only use it to supply values. Consider something like 

  2. Kannon

    • 2016/5/4

    Generally things like column names (or table names) can not be parameterised - and the fact that there are different indices means that it will have to be a different plan internally. So you will have to use concatenation - but be careful to white-list the known column names to prevent sql injection:

  3. Yahya

    • 2021/3/7

    box a parameter to conceive the gather of times to execute i GO statement, many places in APIs identifiers like policy name table column feature can be 

  4. Axton

    • 2016/6/17

    If the difference between two queries is restricted to one or more values, then the effort of preparing several of these queries can be saved by substituting an SQL parameter in place of these values. Instead of sqlite3_prepare() compiling a constant into the SQLite program, it will compile a variable reference instead.

  5. Camden

    • 2015/1/27

    You can click a column header to sort the data in the inspector window You can also use the Database Inspector to run custom SQL queries 

  6. Tatum

    • 2018/3/12

    As all the dot commands are available at SQLite prompt, hence while programming with SQLite, you will use the following SELECT statement with sqlite_master table to list down all the tables created in your database. sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table'; Assuming you have only COMPANY table in your testDB.db, this will produce the following result. tbl_name ----- COMPANY You can list down complete information about COMPANY table as follows −. sqlite> SELECT sql

  7. Connor

    • 2017/5/8

    It is a virtual column, created in the query for displaying the results and it won’t be created on the table. Names and Alias. The alias is a new name for the column that lets you select the column with a new name. The column aliases are specified using the keyword “AS”.

  8. Taylor

    • 2018/8/24

    The Laravel query builder uses PDO parameter binding to protect your application You may not always want to select all columns from a database table.

  9. Jordy

    • 2019/10/25

    Go to Add Column –> Add Custom Column. Change the title to “First date”. Enter the following formula: =fnGetParameter (“Start Date”) The key here is to make sure that the value passed to the parameter function is spelled (and cased) the same as the entry in the first column of the parameter table.

  10. Marku

    • 2018/10/1

    If the statement is replacing an existing table of the same name, then the UDFs and secure SQL UDFs, cannot be referenced in column default expressions.

  11. Kole

    • 2017/12/2

    It means. You can not use bind variables for keywords of sql query (ex: SELECT, UPDATE etc.) You can not use bind variables for objects or their attributes (i.e table names, column names, functions, procedures etc.) You can use them only in place of a otherwise hard coded data.

  12. Rudy

    • 2015/10/20

    Show schema and the content of sqlite_stat tables .headers on|off Turn display of The sqlite3 program is able to show the results of a query in 14 

  13. Morris

    • 2020/8/10

    SQLite Keywords. The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object. The list of keywords is so long that few people can remember them all.

  14. Davis

    • 2020/12/3

    DDL statement as the query parameter. Suppose you have a scenario where you need to pass table name as parameter value and returns all column values, 

  15. Poli

    • 2021/6/13

    Django gives you two ways of performing raw SQL queries: you can use By default, Django figures out a database table name by joining the model's “app 

  16. Brody

    • 2017/11/1

    Name of SQL table. consqlalchemy.engine.(Engine or Connection) or sqlite3.Connection. Using SQLAlchemy makes it possible to use any DB supported 

Comments are closed.

Recent Posts