Re: escaping literals (in libpq)

Hi,

On Apr 3, 2005 7:33 PM, Michael Fuhr <mike(at)fuhr(dot)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
*/