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