MagTun
MagTun

Reputation: 6195

Python: sqlite3.OperationalError: near "<": syntax error (updating sqlite3 field with html source code)

I would like to change all the occurence of sometext (matching all case combination like sometext, SOMETEXT, SOmeTExt) in the note field of the table itemNotes of a sqlite.

Because sqlite doesn't support case insensitive update (I already ask 2 question here and here ), I am using python and regex.

But I get this error : sqlite3.OperationalError: near "<": syntax error matching line of cursor.execute(f'REPLACE

I dont' have a <in my code so I think it's coming from the note field which contains html source code.

Here is my code:

keyword ="sometext"
replacement_word="abc"

# load sqlite3
db = sqlite3.connect(path_to_sqlite)
cursor = db.cursor()

# search for all therm
cursor.execute(f'SELECT * FROM itemNotes  WHERE note like "%{keyword}%"')
print("\nfetch one:")
# itemID_list = cursor.fetchone()

# pour chacun des result, change
for row in cursor.fetchall():
    # print(each)
    row_regex = re.compile(re.escape(keyword), re.IGNORECASE)
    row_regex_replaced = row_regex.sub(replacement_word, row[2])

    rowindex = row[0]
    cursor.execute(
        f'REPLACE INTO itemNotes (note) VALUES ({row_regex_replaced}) where itemID = {rowindex}')

After looking up "sql injection", this is what I came up with :

sql = "REPLACE INTO itemNotes (note) VALUES (?) where itemID = (?)"
data = (row_regex_replaced, rowindex,)
cursor.execute(sql, data)

But now I am getting this error : sqlite3.OperationalError: near "where": syntax error

Upvotes: 0

Views: 404

Answers (1)

DinoCoderSaurus
DinoCoderSaurus

Reputation: 6520

From the sqlite doc:

The REPLACE command is an alias for the "INSERT OR REPLACE" variant of the INSERT command.

An INSERT doesn't have a WHERE clause. The query needs to be written as a "regular" INSERT, and the system will "decide" whether to replace. Something like INSERT into itemNotes (itemID,note) VALUES (?,?). (NB the order of the data list in the above example would need to change).

Upvotes: 1

Related Questions