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

问题描述

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

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.

从我到现在为止所读到的，最好的方法是使用参数，而不是将整个 SQL 分配为字符串.像这样:

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;

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

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?

除了 Delphi 7 标准和 Jedi 之外，我不想使用任何其他组件.

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

推荐答案

安全

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

此代码是安全的，因为您使用的是参数.
参数对于 SQL 注入始终是安全的.

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

不安全

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

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

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;

也不安全

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

因为如果用户名是 ' 或 (1=1);删除表名；--它将导致以下查询:

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; -- '

但是这个密码是安全的

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

因为 IntToStr() 只接受整数，所以不能通过这种方式将 SQL 代码注入到查询字符串中，只有数字 (这正是你想要的，因此被允许)

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)

但是我想做一些参数做不到的事情

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

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?

您必须对照已批准的名称列表检查字符串 tablename.

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.

顺便说一句，您的原始代码有错误:

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

应该是

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

一个(子)查询中不能有两个where

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

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