Re: escaping literals (in libpq)

Hi,

On Apr 3, 2005 7:33 PM, Michael Fuhr <mike@xxxxxxxx> 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
 */

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match