MySQL problem with inserting quote in my QSqlQuery prepare statement

[swankster]

I have been struggling with a QSqlQuery prepare statement to a MySQL database. I am attempting put a set a phrase as a variable. ie phrase_var for "this is my phrase" I need to do a WHERE MATCH of said phrase.

when I run the query within workbench it works perfectly. ```
SELECT * FROM schema.table WHERE MATCH(phrase) AGAINST( '"this is my phrase"' IN BOOLEAN MODE);

"SELECT * FROM schema.table WHERE MATCH( phrase ) AGAINST( '"" + phrase_var + ""' IN BOOLEAN MODE)"

"SELECT * FROM schema.table WHERE MATCH( phrase ) AGAINST( " +R"('")" + phrase_var + R"("')" + " IN BOOLEAN MODE)" );

When viewing the prepared statement with qDebug()<<qry.executedQuery();  always displays as.  with \" instead of "

SELECT * FROM schema.table WHERE MATCH(text) AGAINST( '"this is my phrase"' IN BOOLEAN MODE)"

thankyou in advance for any assistant.

[jsulm]

@swankster It is better to use https://doc.qt.io/qt-6/qsqlquery.html#prepare instead of assembling the query string manually.

[Ronel_qtmaster]

@swankster an exemple
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();

[JonB]

@swankster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

When viewing the prepared statement with qDebug()<<qry.executedQuery(); always displays as. with " instead of "

And that is how literal " characters in a string, so what is the issue?
I'm not going to try to understand whether what you have is correct as you have posted it with the literal ``` stuff all overt the place, but why do you think you have any quoting issue?

Whether MySQL accepts this or there is some other syntax error I cannot say.

[JonB]

@Ronel_qtmaster
Do you have any evidence that bindValue() will be accepted by the MySQL driver for this MATCH ... AGAINST clause? Unless you have tried it you would not know (and I do not, but it would not surprise me if not). For example, LIKE and IN do not accept binds (as far as I recall).

[swankster]

@JonB f
yes, i have used bind as well with the exact same results.

[Ronel_qtmaster]

@JonB I think his problem here is not the plugin but the syntax.And SQL is a general language so why would MYSQL plugin not accept this syntax?

[Ronel_qtmaster]

@swankster please post the code where you used bind, as well as the errors related to it

[JonB]

@swankster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

yes, i have used bind as well with the exact same results.

You have never told us of any "results". Don't know what your actual issue is. Maybe someone else does, but I cannot tell from what you have written. You could show error messages/results but you do not.

And SQL is a general language so why would MYSQL plugin not accept this syntax?

Because I have used MySQL and other SQLs for a long time and have never come across

MATCH(phrase) AGAINST( '"this is my phrase"' IN BOOLEAN MODE);

If you think this statement is somehow in "general SQL" it is not. Each SQL has its own various additions to "standard" SQL (whatever that is), and this would have to fall into that category. I don't know if MySQL has such a clause, and if what OP has written is syntactically correct. That is all I said.

[swankster]

@Ronel_qtmaster
"SELECT * FROM schema.table WHERE MATCH( phrase ) AGAINST( '":phrase"' IN BOOLEAN MODE)" );
qry.bindValue( ":phrase", phrase_var );

executedQuery() returns
SELECT * FROM language.master WHERE MATCH(phrase) AGAINST( '":phrase"' IN BOOLEAN MODE)"

what i need to see in my executequery is
SELECT * FROM schema.table WHERE MATCH(text) AGAINST( '"this is my phrase"' IN BOOLEAN MODE)"
how can I display '"this is my phrase"' without " appearing?

[JonB]

@swankster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

"SELECT * FROM schema.table WHERE MATCH( phrase ) AGAINST( '":phrase"' IN BOOLEAN MODE)" );

If you used the forum's Code (</> button) wehere you are asking about quote characters we could see you are using

"SELECT * FROM schema.table WHERE MATCH( phrase ) AGAINST( '":phrase"' IN BOOLEAN MODE)" );

So instead of '":phrase"' have you tried ':phrase', ":phrase" or :phrase?

[swankster]

@JonB
syntax works perfectly while in workbench. as stated initial post.

the output string is also stated from executedQuery where i am receiving a '"\phrase"' instead of '"phrase"'

[JonB]

@swankster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

syntax works perfectly while in workbench. as stated initial post.

But from workbench you are not using bound variables, are you? Or are you?
And your first post does not use bound variables, does it?
And you still have not said or shown what error you get where in SQL code?
Trying to help, but not seeming to get anywhere.

the output string is also stated from executedQuery where i am receiving a '"\phrase"' instead of '"phrase"'

Copy and paste error messages please. The characters here are vital and I do not think as you have written. And please post inside Code tags, you are asking question about punctuation characters which we cannot even read right!

AGAINST( '":phrase"' IN BOOLEAN MODE)"

This does not look right.

the output string is also stated from executedQuery where i am receiving a '"\phrase"' instead of '"phrase"'

Already said: qDebug() or debugger may show literal characters like ' or " inside strings with backslashes.

[Ronel_qtmaster]

@swankster Why do you want to display it without appearing first? Qt has its syntax

[swankster]

@JonB
yes, i have tried ':phrase', ":phrase" and :phrase
':phrase' returns ':phrase'
:phrase returns ?
it acts as though binding does not work with AGAINST()

in workbench, correct I am not using bound value. that is what im attempting to send to MySQL. it is somewhat working in that it will match any of the words within the phrase to the field. But I need this to check for exact phrase. if it exists to prompt the user that the particular phase is already set in the DB. if it does not exist it automatically tries to add. but the field is unique so it does error when attempting to create it as new if it already exists.

The WHERE MATCH AGAINST() does works in workbench with the exact phrase.

[JonB]

@swankster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

it acts as though binding does not work with AGAINST()

Which is what I suggested might well be the case right from the start.......

@JonB said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

@Ronel_qtmaster
Do you have any evidence that bindValue() will be accepted by the MySQL driver for this MATCH ... AGAINST clause? Unless you have tried it you would not know (and I do not, but it would not surprise me if not). For example, LIKE and IN do not accept binds (as far as I recall).

I did not think he should suggest that to you unless he has tested it actually worked. See the earlier discussion. So unless you know the MySQL driver accepts binding for this clause do not use binding!

Binding is accepted, or not accepted, by the driver on a case by case basis. It is not just a "macro substitution".

[Ronel_qtmaster]

@swankster I would suggest you to try a simple binding syntax with Mysql and see which result you receive.So a statement different that what you want to achieve.If you have a positive result, then binding is working and your syntax is wrong.If not..

[JonB]

@Ronel_qtmaster said in MySQL problem with inserting quote in my QSqlQuery prepare statement:

and your syntax is wrong.If not..

Sorry, but this is not necessarily correct, and you are repeating (what I believe to be) the same mistake as before, in advising the OP to use binding when you have no evidence that this SQL clause, even if correct, accepts parameter binding. You seem to think that anything which is syntactically correct can have bindings put into it, and that is not the case. Please read my earlier posts.

@swankster
Good luck if you can get binding to work for your MATCH ... AGAINST, I suspect you will not. Assuming that is the case, you just need to generate the correct literal sting which you have tested in workshop into C++ code. Qt's QString::arg() is a convenient way to generate QStrings which require C++ variables' values (your phrase_var) inserted into them.

[swankster]

@JonB
binding does not work with Against. looks like I will need to do a different approach. Thanks

[JonB]

@swankster
:) Like I said, just build the (correct, including whatever quoting, and if you are OK on possible SQL injection attacks!) string and pass that without using binding, for this query.