Delphi - Prevent Against SQL Injection

Format function vs Parameters in sql injection scenarios?

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.

How to escape special characters like in the SQL query in order to avoid Injection

Use parameters, and let the database drivers handle that stuff.

SQLQuery1.SQL.Text := 'SELECT * FROM registered WHERE email= :email'+
  ' and login_pass = :password';
SQLQuery1.ParamByName('email').AsString := EMail;
SQLQuery1.ParamByName('password').AsString := Password;

Attempting to prevent SQL injection when referencing an Oracle Package dynamically with JPA

The Oracle dictionary view all_procedures contains a list of all procedures accessible to the current user.

Specifically in the view there are columns OWNER, OBJECT_NAME (=package name), PROCEDURE_NAME.

You may use this view to sanitize the configured input by simple adding an EXISTS subquery such as:

select 
 ?
from dual where exists (
  select null from all_procedures
  where 
   OWNER||'.'||OBJECT_NAME||'.'||PROCEDURE_NAME = upper(?) and
  object_type = 'PACKAGE');

You will have to bind twice the same input parameter.

The query returns no data if there is not procedure with the given name, so you may raise an exception.

The query above expects a full qualified stored procedure name, i.e. owner.package.procedure, you'll have to adapt it slightly if you allow unqualified names (without the owner).

Delphi: what is wrong with my SQL query not returning any results

Your code doesn't do anything to prevent SQL injection, because you're still directly concatenating text to the query. Your SQL syntax is also invalid.

Something like this will work:

procedure TForm1.Button1Click(Sender: TObject);
begin
  AdoQuery1.SQL.Text := 'select * from users where id = :ID');
  AdoQuery1.Parameters.ParamByName('ID').AsString := edit1.Text;
  AdoQuery1.Open;
end;

Using SQL parameters to protect my application against injection attacks

Check the help for "RecordCount". It may raise an exception if the dataset can't determine how many records are returned. What if you remove it and simply check if the dataset not IsEmpty?

---

Related Topics