Search Postgresql Archives

Re: JDBC prepared statement is not treated as prepared statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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]#

 


2013/6/17 高健 <luckyjackgao@xxxxxxxxx>

Hello:

 

I  have one question about prepared statement.

I use Java via JDBC, then send prepared statement to execute.

I thought that the pg_prepared_statments  view will have one record after my execution.

But I can't find.

 

Is the JDBC's prepared statement  differ from  SQL execute by prepare command ?

http://www.postgresql.org/docs/current/static/sql-prepare.html

 

My simple java program is the following:

 

import java.sql.*;

 

public class Test01 {

 

        public static void main(String argsv[]){

        try

         {

           Class.forName("org.postgresql.Driver").newInstance();

           String url = "" ;

   

           Connection con = DriverManager.getConnection(url,"postgres","postgres" );

          

           ///Phase 1:-------------Select data from table-----------------------

 

          

           System.out.println("Phase 1------------------------start");

          

           String strsql = " select * from customers where cust_id = ?";

           PreparedStatement pst=con.prepareStatement(strsql);

 

           pst.setInt(1,3); //find the customer with cust_id of 3.

          

           ResultSet rs = pst.executeQuery();

          

           while (rs.next())

            {

               System.out.print("cust_id:"+rs.getInt( "cust_id"));

               System.out.println("...cust_name:"+rs.getString( "cust_name" ));

           }

           System.out.println("Phase 1------------------------end\n");

          

          

          

           ///Phase 2:-------------Use connection again,to select data from data dictionary-----------------------

          

           System.out.println("Phase 2------------------------start");

          

           strsql = "select * from pg_prepared_statements";

           pst=con.prepareStatement(strsql);          

          

           rs = pst.executeQuery();

          

           while (rs.next())

           {

              System.out.println("statement:"+rs.getString( "statement"));

           }          

           System.out.println("Phase 2------------------------end\n");          

          

           ///Phase 3:-------------Use connection again,to select data from table-----------------------

 

           System.out.println("Phase 3------------------------start");          

           strsql = "select * from customers";

           pst=con.prepareStatement(strsql);          

          

           rs = pst.executeQuery();

          

           while (rs.next())

           {

              System.out.print("cust_id:"+rs.getInt( "cust_id"));

              System.out.println("...cust_name:"+rs.getString( "cust_name" ));

          }           

         

          System.out.println("Phase 3------------------------end\n");           

          

          rs.close();          

          pst.close();

          con.close();

          

       }

        catch (Exception ee)

        {

           System.out.print(ee.getMessage());

       }

        }

 

}

 

The result of it's execution is:

Phase 1------------------------start

cust_id:3...cust_name:Taylor

Phase 1------------------------end

 

Phase 2------------------------start

Phase 2------------------------end

 

Phase 3------------------------start

cust_id:1...cust_name:Smith

cust_id:2...cust_name:Brown

cust_id:3...cust_name:Taylor

Phase 3------------------------end

 

That is to say: my prepared statement is not cached by PG?

Then how to write a  java program to made it's prepared statement realized by PG to treat it as a "prepared statement"?

Thank you.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux