Is QSqlQuery safe?

Christian Ehrlicher

Binding sql values is the way to avoid sql injection since the data is separated by the query. This is nothing Qt specific though.

mrjj

Hi
If you mean safe in terms of someone hacking your binary exe and alter the strings, then no.

But BindValue is the right way to do it and not by string appending anyway :)

TomNow99

@Christian-Ehrlicher @mrjj Thanks!

Do you know how looks that changed by bindValue query?

mrjj

Hi

Do you know how looks that changed by bindValue query?

Im not sure how to read that ?
Could you try other wording ? :)

TomNow99

@mrjj Yes :)

So when I write:

    QSqlQuery query;
    QString id = "1 or 2=2";
    query.prepare(R"(SELECT * FROM tableName WHERE id = )"+id);
    query.exec();

I think that "id" is changed by QString and I get:

SELECT * FROM tableName WHERE id = 1 or 2=2

And this query will go to database.

And how looks query when I use bindValue? I think this is not simple substitute, so I don't get "SELECT * FROM tableName WHERE id = 1 or 2=2", but something other.

mrjj

Hi
Much better :)
there is
QString sql = query.executedQuery();
https://doc.qt.io/qt-5/qsqlquery.html#executedQuery

TomNow99

@mrjj But this isn't exactly what I ask about :) But of course thank you :)

When I execute query.executedQuery() I get:

"SELECT * from tableName where id=(:id)"

Yeah, this is correct. But how database read this query?

Maybe:
SELECT * from tableName where cast(id AS text ) = "1 or 2=2" ?

Here I change id which is int column to text column and int value and logical value (1 or 2=2 ) to its equivalent in text type?

mrjj

@TomNow99
Hi
I actually expected a bit more info o.O :)

For test could you try this code and see if it shows more ?

QString getLastExecutedQuery(const QSqlQuery& query)
{
    QString sql = query.executedQuery();
    int nbBindValues = query.boundValues().size();

    for(int i = 0, j = 0; j < nbBindValues;)
    {
        int s = sql.indexOf(QLatin1Char('\''), i);
        i = sql.indexOf(QLatin1Char('?'), i);
        if (i < 1)
        {
            break;
        }

        if(s < i && s > 0)
        {
            i = sql.indexOf(QLatin1Char('\''), s + 1) + 1;
            if(i < 2)
            {
                break;
            }
        }
        else
        {
            const QVariant &var = query.boundValue(j);
            QSqlField field(QLatin1String(""), var.type());
            if (var.isNull())
            {
                field.clear();
            }
            else
            {
                field.setValue(var);
            }
            QString formatV = query.driver()->formatValue(field);
            sql.replace(i, 1, formatV);
            i += formatV.length();
            ++j;
        }
    }

    return sql;
}

https://stackoverflow.com/questions/5777409/how-to-get-last-prepared-and-executed-query-using-qsqlquery

TomNow99

@mrjj The same, but no problem. The most important for me is that bindValue is safe

mrjj

@TomNow99
Ok :)
I must remember wrong then :)
i was sure it would show the "bound" version.

Christian Ehrlicher

@TomNow99 said in Is QSqlQuery safe?:

Do you know how looks that changed by bindValue query?

Nothing will be changed. I already told you what happens: "... since the data is separated by the query."