Delphi - 防止SQL注入 [英] Delphi - prevent against SQL injection

问题描述

我需要保护应用程序免受SQL注入。应用程序使用ADO连接到Oracle，并搜索用户名和密码进行身份验证。

从我现在读到的最好的方法是使用参数，而不是将整个SQL分配为字符串。如下所示：

  query.SQL.Text：='select * from table_name其中name =：Name和id =：ID '; 
 query.Prepare; 
 query.ParamByName（'Name'）.AsString：= name; 
 query.ParamByName（'ID'）.AsInteger：= id; 
 query.Open; 
  

此外，我正在考虑验证用户的输入，并删除SQL关键字，如删除，插入，选择等...任何与普通ASCII字母和数字不同的输入字符将被删除。

这将确保我的安全级别最低？

我不想使用任何其他组件德尔福7标准和绝地。

解决方案

安全

  query.SQL.Text：='select * from table_name where name =：Name'; 
  

此代码是安全的，因为您正在使用参数。

参数始终是安全的来自SQL注入。

不安全

  var用户名：string; 
 ... 
 query.SQL.Text：='select * from table_name其中name ='+ UserName; 
  

不安全，因为用户名可以是名称;删除table_name;
导致执行以下查询。

  select * from table_name其中name = name; drop table_name; 
  

另外不安全

  var用户名：string; 
 ... 
 query.SQL.Text：='select * from table_name where name ='''+ UserName +''''; 
  

因为如果用户名是'或（1 = 1）;删除Table_name; -
它将导致以下查询：

  select * from table_name where name =''或（1 = 1）;删除Table_name; - '
  

但这段代码是安全的

  var id：integer; 
 ... 
 query.SQL.Text：='select * from table_name其中id ='+ IntToStr（id）; 
  

因为 IntToStr（）只会接受整数所以没有SQL代码可以以这种方式注入查询字符串，只有数字（这正是你想要的，因此允许的）

但是我想要做不了参数的东西

参数只能用于值。它们不能替换字段名称或表名。
所以如果你想执行这个查询

  query：='SELECT * FROM：dynamic_table'; {不工作} 
 query：='SELECT * FROM'+ tableName; {工作，但不安全} 
  

第一个查询失败，因为您无法使用表或字段名称的参数。

第二个查询是不安全的，但这是唯一可以做到这一点。

你如何保持安全？

您必须根据已批准名称的列表检查字符串 tablename 。

  Const 
已批准表：string =（'table1'，'table2'）的数组[0..1] ; 
 
程序DoQuery（tablename：string）; 
 var 
 i：integer; 
已批准：boolean; 
查询：string; 
 begin 
已批准：= false; 
 for i：= lo（ApprovedTables）to hi（ApprovedTables）do begin 
已批准：=已批准或（小写（tablename）= ApprovedTables [i]）; 
结束{for i} 
如果不批准则退出; 
 query：='SELECT * FROM'+ tablename; 
 ... 
  

这是做到这一点的唯一方法，我知道。 / p>

BTW您的原始代码有一个错误：

  query.SQL.Text：='select * from table_name其中name =：Name where id =：ID'; 
  

应该是

 code> query.SQL.Text：='select * from table_name其中name =：Name和id =：ID'; 
  

您不能有两个其中一个（子）查询

I need to protect an application from SQL injection. Application is connecting to Oracle, using ADO, and search for the username and password to make the authentication.

From what I've read until now, the best approach is by using parameters, not assigning the entire SQL as string. Something like this:

query.SQL.Text := 'select * from table_name where name=:Name and id=:ID'; 
query.Prepare; 
query.ParamByName( 'Name' ).AsString := name; 
query.ParamByName( 'ID' ).AsInteger := id; 
query.Open;

Also, I'm thinking to verify the input from user, and to delete SQL keywords like delete,insert,select,etc...Any input character different than normal ASCII letters and numbers will be deleted.

This will assure me a minimum of security level?

I do not want to use any other components than Delphi 7 standard and Jedi.

解决方案

Safe

query.SQL.Text := 'select * from table_name where name=:Name';

This code is safe because you are using parameters.
Parameters are always safe from SQL-injection.

Unsafe

var Username: string;
...
query.SQL.Text := 'select * from table_name where name='+ UserName;

Is unsafe because Username could be name; Drop table_name; Resulting in the following query being executed.

select * from table_name where name=name; Drop table_name;

Also Unsafe

var Username: string;
...
query.SQL.Text := 'select * from table_name where name='''+ UserName+'''';

Because it if username is ' or (1=1); Drop Table_name; -- It will result in the following query:

select * from table_name where name='' or (1=1); Drop Table_name; -- '

But this code is safe

var id: integer;
...
query.SQL.Text := 'select * from table_name where id='+IntToStr(id);

Because IntToStr() will only accept integers so no SQL code can be injected into the query string this way, only numbers (which is exactly what you want and thus allowed)

But I want to do stuff that can't be done with parameters

Parameters can only be used for values. They cannot replace field names or table names. So if you want to execute this query

query:= 'SELECT * FROM :dynamic_table '; {doesn't work}
query:= 'SELECT * FROM '+tableName;      {works, but is unsafe}

The first query fails because you cannot use parameters for table or field names.
The second query is unsafe but is the only way this this can be done.
How to you stay safe?

You have to check the string tablename against a list of approved names.

Const
  ApprovedTables: array[0..1] of string = ('table1','table2');

procedure DoQuery(tablename: string);
var
  i: integer;
  Approved: boolean;
  query: string;
begin
  Approved:= false;
  for i:= lo(ApprovedTables) to hi(ApprovedTables) do begin
    Approved:= Approved or (lowercase(tablename) = ApprovedTables[i]);
  end; {for i}
  if not Approved then exit;
  query:= 'SELECT * FROM '+tablename;
  ...

That's the only way to do this, that I know of.

BTW Your original code has an error:

query.SQL.Text := 'select * from table_name where name=:Name where id=:ID'; 

Should be

query.SQL.Text := 'select * from table_name where name=:Name and id=:ID'; 

You cannot have two where's in one (sub)query

这篇关于Delphi - 防止SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！