Format function vs Parameters in sql injection scenarios?

Question

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 1

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.

Answer 2

Any time you build up an SQL string by concatenating strings together, there is potential for an injection attack, no matter how safe you think access to those strings are. For all you know, someone could run your app inside a debugger, put a breakpoint on the result of QuotedStr(), and modify its contents before allowing Format() to see it.

Using actual SQL parameters is the safest way to go. Not only does it avoid injections, but it also allows the SQL engine to decide how best to format the parameters to its own needs so you don't have to worry about formatting the values in your own code, it works well with strongly-typed languages (like Delphi). Not to mention the performance benefits of being able to prepare the SQL statement on the server side ahead of time before then executing it in your code, even multiple times, drastically reducing the traffic between the client and server and increasing overall performance.

var
  sCustomer : string 
begin 
  AdoSql.CommandText := 'Select SUM(value) result from invoices where customer=:Customer'; 
  AdoSql.Prepared := True;
  ... 
  AdoSql.Parameters['Customer'].Value := sCustomer; 
  AdoSql1.ExecSQL;
  ...
  AdoSql.Parameters['Customer'].Value := sCustomer;
  AdoSql1.ExecSQL;
  ...
  AdoSql.Prepared := False;
end;