pymysql.err.programmingError: (1064)

I'm stuck with storing blob into xampp server. This is the error that I'm getting.

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5\\xd2\\xe0\\xe5yllN\\xbc\\xa7!\\x11!\\x16\\xcftaJ\\xd1\\x863\\xbf\\x9a\\x9aF\\x83\\xe8\\xc9\\...' at line 2")

This is the function that is supposed to save the blob into xampp. Data type is set to mediumblob in xampp. I'm using tkinter and this function is bound to a button.

def save_to_db():
    get_id_no = id_no_var.get()
    get_first_name = first_name_var.get()
    get_middle_name = middle_name_var.get()
    get_last_name = last_name_var.get()
    get_course = course_var.get()
    raw_qr_code_id = str(get_id_no + get_first_name + get_middle_name + get_last_name + get_course)
    final_qr_code_id = str(raw_qr_code_id.replace(" ", ""))
    filename = (final_qr_code_id + ".png")

    raw_image = (filename)
    image = open(raw_image, 'rb')
    image_binary = image.read()

    cursor.execute("""INSERT INTO `student_information` (`id_no`, `first_name`, `middle_name`, `last_name`, `course`, `qr_code_id`, `qr_code_blob`)
                VALUES  ('%s', '%s', '%s', '%s', '%s', '%s', '%s')""" % (get_id_no, get_first_name, get_middle_name, get_last_name, get_course, final_qr_code_id, image_binary))
    connect_db.commit()

Solution

Here is how I managed to insert the data: I first created a table (Used LONGBLOB type because of the size of my picture):

MariaDB [DB]> create table TEST ( id int, file_file LONGBLOB);

Then binarised a picture

image = open('/home/med/Pictures/Screenshot from 2019-12-19 12-48-53.png', 'rb')
image_binary = image.read()

After that I used the following query (notice single quotes around the query and double around the %s)

connection.execute('''INSERT INTO TEST (`id`, `file_file`) VALUES ("%s", "%s")''', (1, image_binary))
# <sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f2b24165580>

Checking if injection worked

MariaDB [DB]> select count(*) from TEST;                      
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.001 sec)

MariaDB [DB]>