Is QSqlQuery safe?

[TomNow99]

Hello,

I would like to query database. So I create very simple query:

SELECT * FROM tableName WHERE id = 

Now I would like to add to this query another id. I can do something like:

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

In example above I create SQL injection - I get all rows from database.

I check documentation and I find bindValue(). I check it:

    QSqlQuery query;
    query.prepare(R"(SELECT * from xyz where id=(:id))");
    query.bindValue(":id", "1 or 2=2");
    query.exec();

And I thing it is safe.

In documentation I don't see any sentence like "bindValue() is safe against SQL Injection. Can you confirm that bindValue is save against SQL Injection? And the second question: Why ( if it is safe ) is it safe? How look that sql injection safe query when use bindValue()?

[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."