Re: escaping literals (in libpq)

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*/