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

Question

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

@Shawn, thanks for this, I change my code to protect it against sql injection — MagTun
– MagTun, Commented May 28, 2019 at 15:10

DinoCoderSaurus · Accepted Answer · 2019-05-28 19:42:15Z

1

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).

answered May 28, 2019 at 19:42

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related