Count number of rows in Pysqlite3

Question

I have to code on python sqlite3 a function to count rows of a table. The thing is that the user should input the name of that table once the function is executed. So far I have the following. However, I don't know how to "connect" the variable (table) with the function, once it's executed. Any help would be great. Thanks

def RT():
    import sqlite3
    conn= sqlite3.connect ("MyDB.db")
    table=input("enter table name: ")
    cur = conn.cursor()
    cur.execute("Select count(*) from  ?", [table])
    for row in cur:
        print str(row[0])
    conn.close()

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Columns and Tables Can't be Parameterized

As explained in this SO answer, Columns and tables can't be parameterized. A fact that might not be documented by any authoritative source (I couldn't find one, so if you you know of one please edit this answer and/or the one linked above), but instead has been learned through people trying exactly what was attempted in the question.

The only way to dynamically insert a column or table name is through standard python string formatting:

cur.execute("Select count(*) from {0}".format(table))

Unfortunately This opens you up to the possibility of SQL injection

Whitelist Acceptable Column/Table Names

This SO answer explains that you should use a whitelist to check against acceptable table names. This is what it would look like for you:

import sqlite3

def RT():
    conn = sqlite3.connect ("MyDB.db")
    table = input("enter table name: ")
    cur = conn.cursor()
    if table not in ['user', 'blog', 'comment', ...]:
        raise ... #Include your own error here
    execute("Select count(*) from {0}".format(table))
    for row in cur:
        print str(row[0])
    conn.close()

The same SO answer cautions accepting submitted names directly "because the validation and the actual table could go out of sync, or you could forget the check." Meaning, you should only derive the name of the table yourself. You could do this by making a clear distinction between accepting user input and the actual query. Here is an example of what you might do.

import sqlite3

acceptable_table_names = ['user', 'blog', 'comment', ...]

def RT():
    """ 
    Client side logic: Prompt the user to enter table name.
    You could also give a list of names that you associate with ids
    """
    table = input("enter table name: ")
    if table in acceptable_table_names:
        table_index = table_names.index(table)
        RT_index(table_index)        

def RT_index(table_index):
    """ 
    Backend logic: Accept table index instead of querying user for 
    table name.
    """
    conn = sqlite3.connect ("MyDB.db")
    cur = conn.cursor()
    table = acceptable_table_names[table_index]
    execute("Select count(*) from {0}".format(table))
    for row in cur:
        print str(row[0])
    conn.close()

This may seem frivolous, but this keeps the original interface while addressing the potential problem of forgetting to check against a whitelist. The validation and the actual table could still go out of sync; you'll need to write tests to fight against that.

Thanks a lot! It worked great! Unfortunately, there are not many videos and pages with help on pysqlite. Thanks again. — Emilio Zaidman, Apr 25 '16 at 20:37
@Grezzo, you bring up a good point. Column names can't be parametrized by `cur.execute` in pysqlite, but that doesn't mean anyone should ignore the possibility of SQL injection. I'm adding a solution to my answer, if you can think of a better one, let me know. — Phillip Martin, Jan 25 '17 at 13:57

Count number of rows in Pysqlite3

1 Answers1

Columns and Tables Can't be Parameterized

Whitelist Acceptable Column/Table Names