2

I'm receiving the error: sqlite3.OperationalError: near "%": syntax error when I try to run the following code. import sqlite3

def getFromDB(DBname,table, url):
    conn = sqlite3.connect(DBname)
    cursor = conn.cursor()
    sql = '''SELECT * FROM %s WHERE URL=%s'''
    stuff = cursor.execute(sql, (table,url))
    stuff = stuff.fetchall()
    return stuff

url = 'http://www.examplesite.com/'
getFromDB('AuthorData.sqlite','forbes',url)

I'm using parameters in my SQL query using %s. Thanks for the help!

atlas cube
  • 47
  • 1
  • 1
  • 6
  • 3
    The [sqlite3](https://docs.python.org/3/library/sqlite3.html) module uses `?` as placeholder. – Ilja Everilä Aug 12 '16 at 19:24
  • Tried that. I get the same error except instead of `%` It shows `?`. – atlas cube Aug 12 '16 at 19:30
  • 2
    Ah yes, missed that you're trying to pass an identifier (table's name). That'll not work using placeholders. That you have to format in to the string. See http://stackoverflow.com/questions/3247183/variable-table-name-in-sqlite for example. – Ilja Everilä Aug 12 '16 at 20:28
  • @IljaEverilä Or better yet, fix your schema so that you don't have to use dynamic table names. – Colonel Thirty Two Aug 12 '16 at 22:04

2 Answers2

1

Some idea: - Using parameter is not available for table name - Using string format is not good because of sql-injection

So first, create a method to make table name safe:

def escape_table_name(table):
    return '"%s"'.format(table.replace('"', '')

Then complete the code with escape table name and parameter using ? for parameter:

    sql = '''SELECT * FROM %s WHERE URL=?'''.format(escape_table_name(table))
    stuff = cursor.execute(sql, (url,))
    stuff = stuff.fetchall()
Le Xuan Tien
  • 11
  • 1
-1

You can use :

sql = '''SELECT * FROM {0} WHERE URL= {1}'''.format(table, url)
Sweta Shaw
  • 9
  • 1