Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!newsfeed.xs4all.nl!newsfeed3.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Return-Path: <python@mrabarnett.plus.com>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
X-Spam-Status: OK 0.001
X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'insert': 0.05; 'string': 0.09; 'method:': 0.09; 'subject:script': 0.09; 'python': 0.11; "'bad": 0.16; '(x,': 0.16; 'from:addr:mrabarnett.plus.com': 0.16; 'from:addr:python': 0.16; 'from:name:mrab': 0.16; 'itself,': 0.16; 'message-id:@mrabarnett.plus.com': 0.16; 'placeholder': 0.16; 'received:192.168.1.4': 0.16; 'received:84.93': 0.16; 'received:84.93.230': 0.16; 'safely.': 0.16; 'tuple': 0.16; 'url:file': 0.16; 'url:py': 0.16; 'wrote:': 0.18; 'header:User- Agent:1': 0.23; 'please?': 0.24; 'question': 0.24; 'script': 0.25; 'query': 0.26; 'pass': 0.26; 'values': 0.27; 'header:In-Reply- To:1': 0.27; 'coded': 0.31; 'url:python': 0.33; 'subject:from': 0.34; 'could': 0.34; 'received:84': 0.35; 'doing': 0.36; 'subject:?': 0.36; 'should': 0.36; 'to:addr:python-list': 0.38; 'to:addr:python.org': 0.39; 'how': 0.40; 'tell': 0.60; "you're": 0.61; 'here:': 0.62; 'protect': 0.79; 'why?': 0.91
X-CM-Score: 0.00
X-CNFS-Analysis: v=2.1 cv=E5NDpMtl c=1 sm=1 tr=0 a=0nF1XD0wxitMEM03M9B4ZQ==:117 a=0nF1XD0wxitMEM03M9B4ZQ==:17 a=0Bzu9jTXAAAA:8 a=u9EReRu7m0cA:10 a=7DZMacBtR5sA:10 a=ihvODaAuJD4A:10 a=IkcTkHD0fZMA:10 a=EBOSESyhAAAA:8 a=9I5xiGouAAAA:8 a=KF0bmsy2SeujFS1EeMAA:9 a=QEXdDO2ut3YA:10
X-AUTH: mrabarnett:2500
Date: Thu, 26 Jun 2014 00:29:39 +0100
From: MRAB <python@mrabarnett.plus.com>
User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: python-list@python.org
Subject: Re: protect psycopg script from sql injection?
References: <CAHByMH0PZKRrie3co9JSUhfr-VufCxO-Jy1K42tAjGvt2mifYg@mail.gmail.com>
In-Reply-To: <CAHByMH0PZKRrie3co9JSUhfr-VufCxO-Jy1K42tAjGvt2mifYg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.15
Precedence: list
List-Id: General discussion list for the Python programming language <python-list.python.org>
List-Unsubscribe: <https://mail.python.org/mailman/options/python-list>, <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <http://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <https://mail.python.org/mailman/listinfo/python-list>, <mailto:python-list-request@python.org?subject=subscribe>
Newsgroups: comp.lang.python
Message-ID: <mailman.11247.1403738987.18130.python-list@python.org>
Lines: 28
NNTP-Posting-Host: 2001:888:2000:d::a6
X-Trace: 1403738987 news.xs4all.nl 2944 [2001:888:2000:d::a6]:36071
X-Complaints-To: abuse@xs4all.nl
Xref: csiph.com comp.lang.python:73597

On 2014-06-25 22:58, celati Laurent wrote:
> Hello,
>
> I coded this following python script via psycopg;
>
> web_service_test.py
> <http://python.6.x6.nabble.com/file/n5062113/web_service_test.py>
>
> 1/ When i execute it, the result is 'bad resquest'. Could you tell me why?
>
> 2/ Could you tell me how to protect this script from SQL injections please?
>
In answer to question 2, don't insert the values into the query string
as you're doing here:

     selectString = "SELECT ST_AsText(geom), cult_lib FROM rpg WHERE 
ST_Intersects(SELECT ST_GeomFromText('POINT(%s %s)',2154), rpg)" % (x, y)

Instead, use the placeholder %s in the query string to indicate where a
values should go and then pass that query string and a tuple of the
values to the .execute method:

     selectString = "SELECT ST_AsText(geom), cult_lib FROM rpg WHERE 
ST_Intersects(SELECT ST_GeomFromText('POINT(%s %s)',2154), rpg)"

     cur.execute(selectString, (x, y))

The database engine will insert the values itself, safely.