Re: JDBC prepared statement is not treated as prepared statement

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(at)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(at)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(at)lex tst]#

2013/6/17 高健 <luckyjackgao(at)gmail(dot)com>

> 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 = "jdbc:postgresql://localhost:5432/postgres" ;
>
>
>
> 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.
>