Re: JDBC prepared statement is not treated as prepared statement - Mailing list pgsql-general

Hi:

Please let me add some contents.

I  have  compared my Java program via JDBC and C program via libpq.

The result is different:

Prepared statement via Java is not recorded in pg_prepared_statements view.

Prepared statement via C is recorded in pg_prepared_statements view.

I don't know how to make PG realize that I am using a prepared statement  when I communicate with PG using Java JDBC.

Can anybody give an idea?

I want this not only for  execution effectiveness, but also for  protecting DB from SQL injection.

It is said that  with common JDBC statement, it is not safe because of SQL injection.

The following is my C code, which is a little long.

I get data using PQprepare and PQexecprepared.

Then I search for the pg_prepared_statements view.

The execution result is:

---------------------

./testprepared

cust_id        cust_name      

3              Taylor         

name           statement      　　　　　　　　　　　　　　　　prepare_time   　　　　　　parameter_typesfrom_sql       

test_stmt      select * from customers where cust_id=$   12013-06-17 16:28:31.70059+08{integer}      f 

----------------------

This is my code:

[root@lex tst]# cat testprepared.c

/*

 * testlibpq.c

 *  Test the C version of LIBPQ, the POSTGRES frontend library.

 */

#include <stdio.h>

#include <stdlib.h>

#include "libpq-fe.h"

static void

exit_nicely(PGconn *conn)

{

 PQfinish(conn);

 exit(EXIT_SUCCESS);

}

int

main()

{

 int   nFields;

 int   i,

       j;

#ifdef DEBUG

 FILE    *debug;

#endif  /* DEBUG */

 ///////////////////////////////////////////////////////////////////////////////

 ///Step1: making connection

 PGconn    *conn;

 PGresult   *res;

 const char *conninfo="postgresql://postgres:postgres@localhost:5432/postgres";

 /* make a connection to the database */

 conn = PQconnectdb(conninfo);

 /* check to see that the backend connection was successfully made */

 if (PQstatus(conn) == CONNECTION_BAD)

 {

  fprintf(stderr, "Connection to database failed.\n");

  fprintf(stderr, "%s", PQerrorMessage(conn));

  exit_nicely(conn);

 }

#ifdef DEBUG

 debug = fopen("/tmp/trace.out", "w");

 PQtrace(conn, debug);

#endif  /* DEBUG */

 ////////////////////////////////////////////////////////////////////////////////////

 ///Step 2, activating prepared statement

 /* start a transaction block */

 res = PQexec(conn, "BEGIN");

 if (PQresultStatus(res) != PGRES_COMMAND_OK)

 {

  fprintf(stderr, "BEGIN command failed\n");

  PQclear(res);

  exit_nicely(conn);

 }

 PQclear(res);

 ////////////////////////////////////////////////////////////////////////////////////

 ///Step 2, activating prepared statement

 const char *stmt_name = "test_stmt";

 const char *stmt = "select * from customers where cust_id=$1";

 Oid param_types[1];

 param_types[0] = 0; ///let db to judge it.

 res = PQprepare(conn, stmt_name, stmt,1,param_types);

 if (PQresultStatus(res) != PGRES_COMMAND_OK)

 {

  fprintf(stderr, "PQprepare failed\n");

  PQclear(res);

  exit_nicely(conn);

 }

 PQclear(res);

 const char* custid = "3";

 const char* param_values[1];

 param_values[0] =custid;

 int param_lengths[1];

 param_lengths[0] = 1;

 int param_formats[1];

 param_formats[0] = 0;

 res = PQexecPrepared(conn, stmt_name, 1, param_values, param_lengths,

                        param_formats, 0);

 if (PQresultStatus(res) != PGRES_TUPLES_OK)

 {

  fprintf(stderr, "PQexecPrepared statement didn't return tuples properly\n");

  PQclear(res);

  exit_nicely(conn);

 }

 /* print out the attribute names */

 nFields = PQnfields(res);

 for (i = 0; i < nFields; i++)

  printf("%-15s", PQfname(res, i));

 printf("\n\n");

 /* print out the instances */

 for (i = 0; i < PQntuples(res); i++)

 {

  for (j = 0; j < nFields; j++)

   printf("%-15s", PQgetvalue(res, i, j));

  printf("\n");

 }

 PQclear(res);

 /* end the transaction */

 res = PQexec(conn, "END");

 PQclear(res);

 ////////////////////////////////////////////////////////////////////////////////////

 ///Step 3, looking for cached status of prepared statements

 /* start a transaction block */

 res = PQexec(conn, "BEGIN");

 if (PQresultStatus(res) != PGRES_COMMAND_OK)

 {

  fprintf(stderr, "BEGIN command failed\n");

  PQclear(res);

  exit_nicely(conn);

 }

 PQclear(res);

 /* define cursor */

 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_prepared_statements");

 if (PQresultStatus(res) != PGRES_COMMAND_OK)

 {

  fprintf(stderr, "DECLARE CURSOR command failed\n");

  PQclear(res);

  exit_nicely(conn);

 }

 PQclear(res);

 /* fetch cursor */

 res = PQexec(conn, "FETCH ALL in myportal");

 if (PQresultStatus(res) != PGRES_TUPLES_OK)

 {

  fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");

  PQclear(res);

  exit_nicely(conn);

 }

 /* first, print out the attribute names */

 nFields = PQnfields(res);

 for (i = 0; i < nFields; i++)

  printf("%-15s", PQfname(res, i));

 printf("\n\n");

 /* next, print out the instances */

 for (i = 0; i < PQntuples(res); i++)

 {

  for (j = 0; j < nFields; j++)

   printf("%-15s", PQgetvalue(res, i, j));

  printf("\n");

 }

 PQclear(res);

 /* close the portal */

 res = PQexec(conn, "CLOSE myportal");

 PQclear(res);

 /* end the transaction */

 res = PQexec(conn, "END");

 PQclear(res);

 ////////////////////////////////////////////////////////////////////////////////////

 ///Step 4, close the connection

 /* close the connection to the database and cleanup */

 PQfinish(conn);

#ifdef DEBUG

 fclose(debug);

#endif  /* DEBUG */

 return 0;

}

[root@lex tst]#