DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
	b=TUcSjI2+VqXYHL3KXlogNdNDqAAcvTNgpnMHlFPwP5ZJrz+KlHbZKK0WAXVQdTX62bhTNMUOUBKPRQMRRInB6aFYGk0mpwYHA/mVez1xxhnzjXnPxmROm6geZJjQ3VAR1HbVx7FR4R/6zhaA8PgpMQ+UAIo0/ZrI7dIoNuNBOQw=
Message-ID: <7104a7370504031011207ed759@mail.gmail.com>
Date: Sun, 3 Apr 2005 20:11:50 +0300
From: Volkan YAZICI <volkan.yazici@gmail.com>
Reply-To: Volkan YAZICI <volkan.yazici@gmail.com>
To: pgsql-interfaces@postgresql.org
Subject: Re: escaping literals (in libpq)
In-Reply-To: <20050403163339.GA86021@winnie.fuhr.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
References: <20050403002747.GA1158@alamut>
	<20050403163339.GA86021@winnie.fuhr.org>

Hi,

On Apr 3, 2005 7:33 PM, Michael Fuhr <mike@fuhr.org> wrote:
> Here's an excerpt from the PQexecParams() documentation:
> 
>     The primary advantage of PQexecParams over PQexec is that
>     parameter values may be separated from the command string, thus
>     avoiding the need for tedious and error-prone quoting and
>     escaping.
> 
> Run some tests: create queries that do simple (but harmless) SQL
> injection, submit them unescaped with PQexec() to verify that the
> injection works, then escape them and submit them with PQexec() to
> verify that escaping prevents the injection, then submit them
> unescaped with PQexecParams() and observe what happens, then escape
> them and submit them with PQexecParams() and observe what happens.

Thanks so much. I engrossed a little bit in my work and forgot the
main point in documentation.

Here's a little piece of code i scratched:

  * template1=# SELECT * FROM mytbl;
  *  usernm |  passwd  |     secret      
 * --------+----------+-----------------
  *  volkan | password | Home address...
 * (1 row)
 */

#include <stdio.h>
#include <libpq-fe.h>

#define BUFLEN 1024

int main(void)
{
    PGconn      *conn;
    PGresult    *res;

    const char  user[] = "volkan";
    const char  passwd[] = "fakepasswd' OR '' = '";
    const char  *values[2];

    char        command[BUFLEN];

    conn = PQconnectdb("user=postgres dbname=template1");

    snprintf(command, BUFLEN, "SELECT secret FROM mytbl WHERE "
             "user = '%s' AND passwd = '%s'", user, passwd);
    res = PQexec(conn, command);
    printf("%d\n", PQntuples(res));
    PQclear(res);

    values[0] = user;
    values[1] = passwd;
    res = PQexecParams(conn, "SELECT secret FROM mytbl WHERE "
                       "user = '$1' AND passwd = '$2'",
                       2, NULL, values, NULL, NULL, 0);
    printf("%d\n", PQntuples(res));
    PQclear(res);

    return 0;
}

/*
  * $ gcc -Wall -lpq execParams.c &&./a.out 
 * 1
 * 0
 */