How to intsert quotation mark `'` into mariaDB using mysql-client?


Problem :

I am using mariaDB(Ver 15.1 Distrib 10.0.17-MariaDB, for osx10.10 (x86_64)) and mysqlclient==1.3.6.

I just want to insert a string into a varcharfield.

import MySQLdb
import json

conn = MySQLdb.connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='',
    db='ng')

cur = conn.cursor()

cur.execute(INSERT INTO `current_table` (`id`, `name`) VALUES (NULL, '{name}');".format(name="Lily' dog"))

conn.commit()

but I always got a error like this:

_mysql_exceptions.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 's dog', NULL)' at line 1")

what should I do if I want to insert quotation mark by mysql-client?



Solution :

According to Amadan's comment, in bobby-tables(the site for preventing SQL injections), it suggest:

Using the Python DB API, don't do this:

Do NOT do it this way:

cmd = "update people set name='%s' where id='%s'" % (name, id)
curs.execute(cmd)

Instead, do this:

cmd = "update people set name=%s where id=%s"
curs.execute(cmd, (name, id))

so in my situation, just modify the execute line to :

cmd = "INSERT INTO `current_table` (`id`, `name`) VALUES (NULL, %s);"
cur.execute(cmd, ("Lily's dog"))

and this can avoid the error which quotation mark leads to.


Mysql Tutorials

Mysql Howto..

Create a website to show data from a remote XML dataset

How can I convert a string to a float in mysql?

How to reset MySQL root user password and privileges

php artisan serve - how do I get mysql to run

How to manage a multi user job list in MySQL?

How to use two values to define order in MySQL but priorize one of them?

How to retrive the last inserted id using mysql?

MySQL: How to set to null only one of the parameters when “ON DELETE”

how to change (Update) column name in table using MySQL workbench?

How do a MySQL REPLACE INTO command in SQL Server?

How to change a value in mysql database using conditions from java code?

How do I check whether column exists in the table?

MySQL how to multiply various column values and return the maximum product

How to retrieve imploded array from a cell in an MySQL database through PHP

How to do an IF statement based on the value of a MySQL table cell?

How to import an Excel file into MySQL using ODBC in Java?

How to next next and previous ids in an ordered search when on a specific id? (MySQL)

How to insert null value in datetime column of mysql in c# [duplicate]

How to log Windows 7 network traffic & disk usage with MySQL?

How to fetch the most recent timestamped record for the same id from MySQL table? [duplicate]

How to import this xml feed into sql with auto update?

How to associate description with table fields in MySQL?

How to dont let users save the same item twice in mysql

how to use like on a long string value in mysql

How to get the max value from a column with alphanumeric strings?

mysql: How to free space (innodb)

How to update column text value with the output of multiple if statement in mysql?

How to extract month and year from MySQL?

How to edit datatable's rows (JAVA + Primefaces + JSF + MYSQL)

How can I combine my columns into one to create a URI in MySQL?