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.
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.
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.
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?
Psycopg 2 is mostly implemented in C as a libpq wrapper, resulting in being both efficient and secure. It features client-side and server-side cursors, asynchronous communication and notifications, COPY support.
SQL injections rely on this type of vulnerability. Any time user input is used in a database query, there's a possible vulnerability for SQL injection. The key to preventing Python SQL injection is to make sure the value is being used as the developer intended.
The benefits of using an ORM tool include quick generation of an object layer to communicate to a relational database, standardize code templates for these objects, and that they usually provide a set of safe functions to protect against SQL Injection attacks.
Using the Error class of Psycopg2, we can handle any database error and exception while working with PostgreSQL from Python. Using this approach, we can make our application robust. The error class helps us to understand the error in detail. It returns an error message and error code if any.
AsIs
is unsafe, unless you really know what you are doing. You can use it for unit testing for example.
Passing parameters is not that unsafe, as long as you do not pre-format your sql query. Never do:
sql_query = 'SELECT * FROM {}'.format(user_input)
cur.execute(sql_query)
Since user_input
could be ';DROP DATABASE;'
for instance.
Instead, do:
sql_query = 'SELECT * FROM %s'
cur.execute(sql_query, (user_input,))
pyscopg2
will sanitize your query. Also, you can pre-sanitize the parameters in your code with your own logic, if you really do not trust your user's input.
Per psycopg2
's documentation:
Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.
Also, I would never, ever, let my users tell me which table I should query. Your app's logic (or routes) should tell you that.
Regarding AsIs()
, per psycopg2
's documentation :
Asis()... for objects whose string representation is already valid as SQL representation.
So, don't use it with user's input.
You can use psycopg2.sql to compose dynamic queries. Unlike AsIs
it will protect you from SQL injection.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With