Inserting a Table Name into a Query Gives SQLite3.Operationalerror: Near "": Syntax Error

Inserting a table name into a query gives sqlite3.OperationalError: near ?: syntax error

You cannot use SQL parameters to be placeholders in SQL objects; one of the reasons for using a SQL parameters is to escape the value such that the database can never mistake the contents for a database object.

You'll have to interpolate the database objects separately; escape your identifiers by doubling any " double quote parameters and use

cur.execute('SELECT COUNT(Name) FROM "{}" WHERE Name=?'.format(group.replace('"', '""')), (food,))

and

cur.execute('INSERT INTO "{}" VALUES(?, ?)'.format(group.replace('"', '""')), (food, 1))

and

cur.execute('UPDATE "{}" SET Times=? WHERE Name=?'.format(group.replace('"', '""')),
            (times_before + 1, food))

The ".." double quotes are there to properly demark an identifier, even if that identifier is also a valid keyword; any existing " characters in the name must be doubled; this also helps de-fuse SQL injection attempts.

However, if your object names are user-sourced, you'll have to do your own (stringent) validation on the object names to prevent SQL injection attacks here. Always validate them against existing objects in that case.

You should really consider using a project like SQLAlchemy to generate your SQL instead; it can take care of validating object names and rigorously protect you from SQL injection risks. It can load your table definitions up front so it'll know what names are legal:

from sqlalchemy import create_engine, func, select, MetaData

engine = create_engine('sqlite:////path/to/database')
meta = MetaData()
meta.reflect(bind=engine)
conn = engine.connect()

group_table = meta.tables[group]  # can only find existing tables
count_statement = select([func.count(group_table.c.Name)], group_table.c.Name == food)
count, = conn.execute(count_statement).fetchone()
if count:
    # etc.

sqlite3.OperationalError: near ?: syntax error

Yes, ? cannot be used for table names or field names, because those don't get quoted. It is unusual that this function doesn't already know the table name. How could it know the fields, but not the table?

And it's silly to set the title, when you are doing an update based on the title.

What is that loop trying to do? It looks like you're trying to return False if the title is found in any record, but you KNOW it has to be there, because you just did an UPDATE WHERE title='title'. In any case, "is" is the wrong operator for that.

def db_edit(db , cr , table , new_value , title):
    time = check_txt_len(new_value , 8 , "0")
    tu = (time[0:2] , time[3:5] , time[6:] , title)
    cr.execute(f"UPDATE {table} SET hour=?, min=?, period=? WHERE title=?", tu)
    return True

sqlite3.OperationalError: near X: syntax error in CREATE TABLE statement

Your CREATE TABLE is incorrect: it doesn't specify a name for the first ('integer primary key') column. SQLite now thinks that the field is named integer, and has no data type defined. You probably wanted to have an INTEGER PRIMARY KEY field, because that is very efficient. To do so, respect the CREATE TABLE syntax, and give it a name:

CREATE TABLE items
( id INTEGER PRIMARY KEY
, x  DOUBLE
, y  DOUBLE
);

As a side note: I've defined X and Y as doubles, since specifying the type is just good practice, and is also slightly more efficient. Of course, if you want to put text in them, define them as TEXT. Or if you want them to contain primarily integers, define them as INTEGER. Only ever leave out the data type if you really don't know what kind of data you'll be putting in there.

Next, since the INSERT statement only expects field names (and not their full definition), SQLite throws a Syntax Error -- rightly so.

And finally, don't you think it a bit silly to put 'today' (a text value) into an integer column?!?

---

Edit: since you say X and Y are variables, might as well put you on the right track at to binding those variables to the SQL statement:

curs.execute("INSERT INTO items (X, Y) VALUES (:X, :Y)", {X: X, Y: Y})

I've left out the id primary key field, since SQLite will automatically generate that if it's not present. If you want to pass an explicit value, you can. But take care that it is an integer, and that it is unique!

The :X and :Y parameters in the SQL statement refer to the X and Y members of the dictionary that is passed as second parameter to the execute statement.

Binding parameters is both safer and faster than including the parameters into the SQL string itself.

SQL OperationalError: near %: syntax error when using inserting values into a table with executemany

Well, I accidentally found an answer when looking up a solution for another problem. I should be using "?" instead of "%s".

I hadn't realised that there were different versions of SQL, and I am apparently using SQLite3 that uses "?" and not "%s". Hope this helps other idiots like myself.

sqlite3.OperationalError: near index: syntax error

INDEX is a keyword in SQLite3. Thus, it'll be parsed as a keyword. There are several ways around this, though.

According to the documentation, you could use backticks or quote marks to specify it as a table name. For example,

CREATE TABLE IF NOT EXISTS `index` ...

or

CREATE TABLE IF NOT EXISTS "index" ...

may work.

You can pass arguments to your sql statement from the execute() command. Thus,

create = r'''CREATE TABLE ... VALUES(?,?,?,?);'''  # use ? for placeholders
c.execute(create, (url, title, description, keywords))  # pass args as tuple

This is more secure compared to formatting your arguments directly with Python.

Note also that SQLite's syntax for autoinc is AUTOINCREMENT without the underscore and they require the field to also be an INTEGER PRIMARY KEY.

---

Related Topics