Обсуждение: problem with single quote in postgres 8.3.5

I have a little php-script to  help me learn foreign languages
In my php-code I have the following line:
$query="update wordlist set known=true where dutch='".$preceding."'";

This worked fine on a system running postgres 8.1, however in version
8.3.5 something changed which makes records with the dutch-field
containing single quotes unfindable. You could argue that it's a bad
practice to include records with single quotes in a field but in
language related applications it's unavoidable as single quotes are
used and escaping every single quote when adding data to the database
is something you can't ask the user.

example of a dutch field which causes the problem: hij zei: 'het is
waar'

I guess there must be a way to escape the single quotes automatically
without rebuilding the database

any help appreciated

hugo

On 07/03/2009 13:53, hugocoolens wrote:
> I have a little php-script to  help me learn foreign languages
> In my php-code I have the following line:
> $query="update wordlist set known=true where dutch='".$preceding."'";
>
> This worked fine on a system running postgres 8.1, however in version
> 8.3.5 something changed which makes records with the dutch-field
> containing single quotes unfindable. You could argue that it's a bad
> practice to include records with single quotes in a field but in
> language related applications it's unavoidable as single quotes are
> used and escaping every single quote when adding data to the database
> is something you can't ask the user.
>
> example of a dutch field which causes the problem: hij zei: 'het is
> waar'
>
> I guess there must be a way to escape the single quotes automatically
> without rebuilding the database

I'm not completely sure what you're looking for, but do you know the PHP
function pg_escape_string()? -

  $query = "update wordlist set known=true where dutch='"
    . pg_escape_string($preceding) . "'";


HTH,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Sat, Mar 07, 2009 at 05:53:21AM -0800, hugocoolens wrote:
> I have a little php-script to  help me learn foreign languages
> In my php-code I have the following line:
> $query="update wordlist set known=true where dutch='".$preceding."'";

With this kind of line, you are inviting an SQL injection as
illustrated below:

http://xkcd.com/327/

Instead, use pg_prepare() and pg_execute() for this kind of thing.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate