动态生成的SQL查询的安全性 [英] Security with dynamically generated sql query

问题描述

我正在创建侧边栏搜索(通过单击选项)，并选择单击的变量来创建sql查询.

I'm creating a sidebarsearch(by clicking options), and picking the variables clicked to create sql query.

更具体地说:
1.用户在侧栏中选择选项.
2.我根据添加了params作为'param1 = value&'...
的那些选择创建str(作为要调用的URL).3.通过$ _GET根据参数对Ajax调用php控制器->模型->查询dababase.

More specifically:
1. user selects options in sidebar.
2. I create str(as url to be called) based on those selections adding params as 'param1=value&'...
3. ajax call to php controller->model->query dababase based on params via $_GET.

最后，我使用准备好的语句，但是从理论上讲，攻击者可以编写自己的url.为了避免这种情况，我预先指定了允许的值($ keysArr)，如果$ _GET vars不存在，脚本就会死掉.也可以简单地将int()添加到期望的数值上，因此php如果不是int则会抛出错误.

I use the prepared statements in the end but in theory the attacker can make up their own url. To avoid this i pre specify the allowed values($keysArr) and if $_GET vars are not there the script dies. Also simply adding int() to expected numeric values, so php will trow and error if it's not int.

$keysArr = ['x', 'y', 'z'];

foreach ($ArrfromGET as $key => $value) {
    if (!in_array($key, $keysArr)) {
        die("don't attack me");
    }
}

我这样做正确吗?
搜索基于动态生成的值，因此我不确定该怎么做.该代码基于( https://www.w3schools.com/js/js_ajax_database.asp );

Am i doing this correctly ?
The search is based on values generated dynamically so i'm not sure what to do about it. The code is based on(https://www.w3schools.com/js/js_ajax_database.asp);

推荐答案

您可以尝试一下.

注意，我只是展示如何在SQL中保护动态查询

Note I'm just showing how to secured dynamic query in SQL

DECLARE @ParameterDefinition NVARCHAR(MAX) 
 SET @ParameterDefinition = '  
  @P_Name NVARCHAR(50)  
 , @P_Address NVARCHAR(50)';
DECLARE @SQL  NVARCHAR(MAX);
DECLARE @Name  NVARCHAR(50) = '' //set here value
DECLARE @Address NVARCHAR(50) = '' //set here value

SET  @SQL = 'SELECT * FROM USER WHERE Name = @P_Name OR Address = @P_Address'
 
 
 EXECUTE sp_executesql @SQL, @ParameterDefinition,
 @P_Name = @Name,
 @P_Address = @Address

这篇关于动态生成的SQL查询的安全性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！