Thomas Zangl <[EMAIL PROTECTED]> wrote:
I am currently in doubt if the usage of sqlite3_prepare(...) makes my
application safer.
It is usually recommended to preapre a SQL statement before using it.
In my case, I have no need to re-use them so a simple sqlite3_exec
would
be sufficient. On the other side I have been told that preparing the
sql
statement makes the application better protected against overflows
(buffer, integer) or sql injection.
It's not sqlite3_prepare vs sqlite3_exec per se makes it safer - it
doesn't. sqlite3_exec is implemented internally in terms of
sqlite3_prepare anyway. What is safer is using parameters vs just
concatenating user-supplied values into query string. Consider:
string userInput;
string sql = "update UserPrefs set innocuousPref='" + userInput + "'
where userid=123;";
sqlite3_exec(db, sql.c_str(), ...);
A malicious user could supply something like this in userInput:
x'; update Users set password=''; --
All the passwords are reset, and the attacker can now log into any
account.
Compare with this program:
string userInput;
string sql = "update UserPrefs set innocuousPref=? where userid=123;";
sqlite3_stmt* stmt;
sqlite3_prepare(db, sql.c_str(), -1, &stmt, 0);
sqlite3_bind_text(stmt, 1, userInput.c_str(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt);
A little bit more verbose, but safe from injection attack. No matter
what weird string might be supplied, it will only affect one field of
one record in UserPrefs table.
Is that true? How does sqlite3_prepare internally work? I usually
sanitize every user input before using it as a parameter
Why go to the trouble, if you can use SQL parameters?
Is that as safe as preparing a statement?
Possibly. But it's so much more work, and there's a risk that a clever
attacker outsmarts your escaping rules and manages to get a malicious
string past your defences.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
