Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!feeder.news-service.com!newsfeed.xs4all.nl!newsfeed6.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail Return-Path: 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; 'newbie': 0.03; 'string.': 0.04; 'reference:': 0.07; 'sql.': 0.07; 'python': 0.07; 'from:addr:timgolden.me.uk': 0.09; 'from:name:tim golden': 0.09; 'message-id:@timgolden.me.uk': 0.09; "module's": 0.09; 'substitution': 0.09; 'this:': 0.11; 'written': 0.12; 'wrote:': 0.14; '"...': 0.16; 'coder': 0.16; 'received:74.55.86': 0.16; 'received:74.55.86.74': 0.16; 'received:smtp.webfaction.com': 0.16; 'received:webfaction.com': 0.16; 'recipes': 0.16; 'cc:no real name:2**0': 0.20; 'cc:2**0': 0.20; 'code': 0.22; 'header:In- Reply-To:1': 0.22; 'cc:addr:python-list': 0.22; 'values': 0.23; "what's": 0.24; "i'm": 0.26; 'instead': 0.26; 'string': 0.29; 'etc.)': 0.29; "python's": 0.29; '(the': 0.30; 'cc:addr:python.org': 0.31; 'tjg': 0.31; 'all,': 0.31; "skip:' 10": 0.32; 'called': 0.32; '...': 0.32; "i've": 0.33; 'received:192': 0.34; 'header:User-Agent:1': 0.35; 'flag': 0.35; 'subject:use': 0.35; 'response': 0.36; 'received:192.168': 0.37; 'two': 0.37; 'database': 0.38; 'used': 0.38; 'end': 0.39; 'where': 0.39; 'would': 0.40; "it's": 0.40; 'might': 0.40; 'from:addr:mail': 0.60; 'vary': 0.60; 'simple': 0.60; 'best': 0.60; 'full': 0.62; 'details': 0.64; 'strange': 0.65; 'circle': 0.68; 'alternative': 0.69; 'introduce': 0.78; 'to:none': 0.92 Date: Mon, 18 Apr 2011 09:44:40 +0100 From: Tim Golden User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9 MIME-Version: 1.0 CC: python-list@python.org Subject: Re: strange use of %s References: <4dabf65a$0$18250$4fafbaef@reader2.news.tin.it> In-Reply-To: <4dabf65a$0$18250$4fafbaef@reader2.news.tin.it> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.12 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 39 NNTP-Posting-Host: 82.94.164.166 X-Trace: 1303116284 news.xs4all.nl 34849 [::ffff:82.94.164.166]:52001 X-Complaints-To: abuse@xs4all.nl Xref: x330-a1.tempe.blueboxinc.net comp.lang.python:3475 On 18/04/2011 09:29, Tracubik wrote: > Hi all, > i'm reading a python tutorial in Ubuntu's Full Circle Magazine and i've > found this strange use of %s: > > sql = "SELECT pkid,name,source,servings FROM Recipes WHERE name like '%%%s% > %'" %response > > response is a string. I've newbie in sql. > > why do the coder use %%%s%% instead of a simple %s? > why he also use the ''? Two parts to this answer. The straightforward one: because the SQL string needs to end up looking like this: "... WHERE name LIKE '%abcd%'" and since it's being generated by Python's string substitution, the surrounding percents need to be doubled up in the original string to be left as single in the final string. An alternative in a modern Python might be to use string formatting: "... WHERE name LIKE '%{}%'".format (response) HOWEVER... this is not the best way to introduce Python values into a SQL string. It's better to use the db module's string substitution flag (often ? or :field or, confusingly, %s). This is because the approach above lends itself to what's called SQL injection. Obligatory xkcd reference: http://xkcd.com/327/ The code would be better if written something like this: sql = "SELECT ... WHERE name LIKE '%' + ? + '%'" q = db.cursor () q.execute (sql, [response]) (The details will vary according to the database being used etc.) TJG