psycopg2和SQL注入安全性 [英] psycopg2 and SQL injection security

问题描述

我正在编写一个类，将其用作更大的建模算法的一部分.我的部分进行空间分析以计算从某些点到其他点的距离.条件多种多样，包括返回距离的数量，截止距离等.

I am writing a class to be used as part of a much larger modeling algorithm. My part does spatial analysis to calculate distances from certain points to other points. There are a variety of conditions involving number of returned distances, cutoff distances, and etc.

当前，项目规范仅指示硬编码情况.即功能#1需要列出从点集A到点集B的所有距离，都在500m之内.功能#2需要列出从点集C到点集D的所有距离..."，依此类推.

Currently, the project specification only indicates hardcoded situations. i.e. "Function #1 needs to list all the distances from point set A to point set B within 500m. Function #2 needs to list all the distances from point set C to point set D..." and so on.

我不想对这些参数进行硬编码，也不希望开发模型的下一阶段的人，因为显然他们想调整参数或可能在其他项目中重用该算法，有不同的条件.

I don't want to hardcode these parameters, and neither does the person who is developing the next stage of the model, because obviously they would like to tweak the parameters or possibly re-use the algorithm in other projects where they will have different conditions.

现在的问题是我正在使用psycopg2来执行此操作.这是我工作的标准，所以我别无选择.我已经读到，由于SQL注入的明显原因，将要放入执行的查询中的参数作为参数公开是一个非常糟糕的主意.但是，我认为psycopg2自动清除了SQL输入.我认为问题出在使用AsIs函数.

Now the problem is that I am using psycopg2 to do this. This is the standard where I work so I do not have a choice of deviating from it. I have read that it is a very bad idea to expose parameters that will be put into the executed queries as parameters due to the obvious reason of SQL injection. However, I thought that psycopg2 automatically sanitized SQL input. I think that the issue is using the AsIs function.

简单的解决方案是按照项目中指定的方式对其进行硬编码，但这对我来说是懒惰和草率的.我不喜欢做懒散的工作.

The easy solution is just to hardcode it as specified in the project but this feels lazy and sloppy to me. I don't like doing lazy and sloppy work.

允许用户输入要输入到psycopg2执行的查询中的参数是否绝对安全?还是仅使用AsIs使其不安全?如果我想让用户能够输入这些参数，我是否必须承担起自己的义务来对输入进行汉化处理，如果是的话，有没有一种快速简便的方法来进行输入，例如使用另一个python库或什么东西?

Is it at all safe to allow the user to input parameters that will be input into a psycopg2-executed query? Or is it just using AsIs that makes it unsafe? If I wanted to allow the user to be able to input these parameters, do I have to take the responsibility upon myself to santitize the inputs, and if so, is there a quick and easy way to do it, like with another python library or something?

推荐答案

您可以使用 psycopg2. sql 组成动态查询.与AsIs不同，它将保护您免受SQL注入.

You can use psycopg2.sql to compose dynamic queries. Unlike AsIs it will protect you from SQL injection.

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