SQL error when using parameters

SPlatten

In my application I process the JSON:

{"command":"query"
    ,"sql":"INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?,?);"
   ,"args":["current_date()","current_time()"]}

Here is the code that interprets this JSON:

QString strCmd = citCmd.value().toString();
if ( strCmd.compare(clsModSQL::mscszCmdQuery) != 0 ) {
    robjResponse.insert(clsModSQL::mscszError  ,"Invalid command!");
    return false;
}
QJsonObject::const_iterator citSQL = robjCmd.find(clsModSQL::mscszSQL);
bool blnNoSQL = true;
QString strSQL;

if ( citSQL != robjCmd.end() ) {
    strSQL = citSQL->toString();

    if ( strSQL.trimmed().isEmpty() != true ) {
        blnNoSQL = false;
    }
}
if ( blnNoSQL == true ) {
//No SQL supplied, cannot proceed!
    robjResponse.insert(clsModSQL::mscszError, "No SQL supplied!");
    return false;
}
QJsonArray aryArgs;
QSqlQuery query;
query.prepare(strSQL);

if ( citArgs != robjCmd.end() ) {
    QJsonArray aryArgs = citArgs->toArray();
    for( int i=0; i<aryArgs.count(); i++ )  {
        QVariant varArg(aryArgs.at(i).toVariant());
        query.addBindValue(varArg);
    }
}
query.exec();
//Any errors
QSqlError err = query.lastError();
if ( err.type() != QSqlError::NoError ) {
    QString strErrType("[" + QString(err.type()) + "]");
    robjResponse.insert(clsModSQL::mscszError, strErrType + err.text());
    return false;
}

Where citCmd is of type QJsonObject::const_iterator that has found the command in the JSON and has been verified as found.

I've single stepped to the error checking at the bottom of the above code and it results in the following error:

"[]Incorrect date value: 'current_date()' for column `test`.`tbltest`.`dtWhen` at row 1 QMYSQL3: Una"

What have I done wrong because if I take the SQL and the parameters and put into MySQLWorkbench it works ?

The fields I'm trying to insert into:

Column:dtWhen, Type:date, Default Value:NULL, Nullable:YES
Column:tmWhen, Type:time, Default Value:NULL,Nullable:YES

If I change the SQL to:

"INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());"

So the arguments are not dropped in to replace the ? it works perfectly, but I need the ability to pass in the parameters, suggestions and help very much appreciated.

KroMignon

@SPlatten said in SQL error when using parameters:

So the arguments are not dropped in to replace the ? it works perfectly, but I need the ability to pass in the parameters, suggestions and help very much appreciated.

I think your analysis if wrong and your implementation also ;)
What you want is:

INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());

And what you get is, I guess:

INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES ("current_date()","current_time()");

With QSqlQuery::addBindValue(), the parameter will be converted to an SQL compatible string. In your case the QVariant holds a string, so it will convert to SQL string (with escape sequences and so on).

I am not sure, but I think using query.addBindValue(varArg, QSql::In | QSql::Binatry); should work.

JonB

@SPlatten
I was about to type in a similar analysis to @KroMignon, who has explained it admirably!

In general, you are going to have find a generic way to pass something which is a SQL expression to evaluate (current_date(), current_time()) as a bound parameter. This could potentially happen all over the place in the text of the commands you read in. You will need to distinguish literal numbers from literal strings from SQL functions/expressions etc. when deciding what to construct from the queries.

Let's hope his query.addBindValue(varArg, QSql::In | QSql::Binary) works. Though why QSql::Binary ("to indicate that the data being transferred is raw binary data.") will treat a string (current_date()) as something to pass as-is for later evaluation I do not know.

SPlatten

@KroMignon , the issue I have is that originally I did exactly that, however it will not compile like that because current_date() and current_time() only have meaning when executed by the SQL server, they mean nothing in JSON and cannot be evaluated, so they become undefined. The second parameter of addBindValue defaults to QSql::In.

JonB

@SPlatten
If indeed it does not work with QSql::In | QSql::Binary then you (seem to) have no other choices for the parameter which will work from Qt client. That would mean it "cannot be done via a bound parameter", and you would need to handle such cases via direct interpolation into the query string instead of via a bound parameter. I did a look around and was unable to find any examples/questions where a bound value from Qt is set to an expression to be evaluated server-side, so we do not know whether it is supported. Unless @KroMignon has reason to believe it should be for the proposal he offers?

SPlatten

@JonB , Just tried using QSql::In | QSql::Binary combination as the second parameter and removed the quotes around the arguments, it still doesn't like it.

It works as in my original post I drop in the arguments inline without quotes.

JonB

@SPlatten
It won't work "without the quotes", assuming you mean something like

query.addBindValue(current_date(), QSql::In | QSql::Binary)

as that won't compile. @KroMignon must intend you to try:

query.addBindValue("current_date()", QSql::In | QSql::Binary)

and hope that will cause the driver to pass across plain current_date() for evaluation at server-side. Which as I've said, unless @KroMignon knows better, I would doubt does that: more likely, it does something about passing the bytes of "current_date()" in some binary representation.

It works as in my original post I drop in the arguments inline without quotes.

Indeed. That's why I said, unless you can find a way to make it work with a bound parameter, you will have to recognise a case like this (e.g. parameter is neither a quoted string nor a number) and replace the ? directly in-line instead of trying to use any placeholder parameter.

SPlatten

@JonB , No, thats not what I meant. If I write this:

INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());

It works fine, if I try:

INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (?, ?);

And try:

query.addBindValue("current_date()", QSql::In | QSql::Binary);
query.addBindValue("current_time()", QSql::In | QSql::Binary);

It does not work. Correction, it may work when its hard coded like that, but that isn't how I have it, the SQL and arguments are passed in via JSON. If I put the SQL functions into the JSON without quotes then the JSON fails.

NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.

JonB

@SPlatten
You say "No, thats not what I meant.", but I do understand that is what you meant!

I am suggesting: if indeed @KroMignon's

query.addBindValue("current_date()", QSql::In | QSql::Binary);

does not work --- and I was questionable that it would, as I don't think that is what QSql::Binary is for/does, but he may know better that I --- that means there is nothing left for the variable type/QSql::ParamType for you to try from Qt which would work for what you need to do here.

If that is true you must go for the literal, in-line-interpolated

INSERT INTO test.tblTest (`dtWhen`,`tmWhen`) VALUES (current_date(),current_time());

in the cases where a parameter is something like neither a literal string not a number, it cannot be done via bindValue().

As I said, I have had a look around. Although it's not easy to Google for, there simply does not seem to be any question about how to bind a value which is a SQL expression instead of a literal. So I can find neither a solution nor even someone who wants to do as you want to do (although it's quite reasonable) via a bound value.

This has nothing to do with your JSON. Of course anything you put there must be legitimate JSON, there is no point in "If I put the SQL functions into the JSON without quotes then the JSON fails".

NVM, I have chosen to treat this as a special case and if SQL functions are required then I will just document it that they have to be embedded into the query.

Yep, that's OK. Or, as I said, you could recognise that a parameter which is neither a quoted string nor a number (or similar) is to be interpolated directly into the query string. Though beware of injection attacks!

JonB

@SPlatten
On a related but different note, how have you decided that the user should specify a literal string parameter in your JSON file?

Say the query is

SELECT * from names_table WHERE name = ?

and the user wants to look for Simon. In your JSON, do you require the user to write

"args":["Simon"]

or

"args":["'Simon'"]

? I hope it is the latter, but do you actually require the former?

SPlatten

@JonB , I've used ? and argument binding before and it works very nicely, the single quotes are not required, passing the argument as:

"args":["Simon"]

Will work just fine.

JonB

@SPlatten
Then I think you may run into problems constructing queries because you cannot distinguish, say, what is to be a string parameter from a numeric parameter. You have (presumably) made it impossible to pass an argument as a numeric, everything will go as a string. That is not what you would do in code when constructing a query which has a numeric parameter, e.g you would write

query.addBindValue(10);

rather than

query.addBindValue("10");

I leave you to contemplate the consequences.

Actually, this depends again on what you require from the user. Would they specify in your JSON a number for an argument via

"args":["10"]

or

"args":[10]

?

SPlatten

@JonB , actually I've played around with this in the past the routines are quite smart in the way they handle parameters, which is why its better and safer to use ? and use parameters rather than embedding the parameter values inline. The code handles the interpretation of the type and adds quotes to the query automatically, of course you won't see this but putting quotes on the data is not required.

JonB

@SPlatten
Yes, I realize that about parameters. They will respect the type of the C++ variable/JSON variant type you have read the parameters in as. That's why JSON "args":["10"] will produce a different C++/SQL type from "args":[10].

Anyway, sounds like you have this under control.