Insert Strings that contain “ ` ” or “ ' ” to the database table - Qt

Question

I have to insert some strings to MySQL database. The problem is that every time I use " ` " or " ' " it causes errors in the QSqlquery execution. How can I prevent this?

Answer 1

Always use bind variables when running your query and you will never have problems with special characters in SQL queries. Here is an example from the documentation:

QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
              "VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();

Answer 2

` and ' are comment in SQL, you have to "protect" them with a backslash \ like so

Select bla
From blo
where name = "some \`test\`"

Answer 3

You should add escape sequence to add such type of special characters like ', `, \, "

Please add them by adding \ before them.

e.g.

For ' use \'

For " use \"

For \ use \\

For ` use \`

Answer 4

// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));

in php, You can use mysql_real_escape_string function: http://php.net/manual/en/function.mysql-real-escape-string.php

Hope will help you!