Salvador Salvador - 3 months ago 21
SQL Question

Format function vs Parameters in sql injection scenarios?

I know about the uses of parameters in sql sentences, But just for curiosity is safe to use the Format function to prevent sql injections instead of use paramters.

like this sample

sCustomer : string
begin
AdoSql.CommandText:=Format('Select SUM(value) result from invoices where customer=%s',[QuotedStr(sCustomer)]);
end;

Answer

That would probably be secure against SQL injection, assuming QuotedStr works as expected and there are no edge cases that can break it. (Which is by no means guaranteed. As Linas pointed out in a comment, MySql lets you use \' to escape out quotes. Other DBMSs probably have similar capabilities. An attacker with enough theoretical knowledge of the system would be able to exploit them.)

However, even if QuotedStr was good enough, it's still better to use parameters for a different reason: performance. When you separate your parameters from your query, you can end up sending the exact same query code multiple times with different parameters. If you do that, the database can cache a lot of the work it does in computing the query, so your DB access gets faster. That doesn't work (or at least not as well) when you mix the parameters into the query code itself.