Mon, Jun 24, 2002 at 09:20:32PM +0200 scrivesti
> >So I don't care about escapes, but the data-type is knwon from the statements
> >and I guess the driver fix them accordingly.
>
> The problem is not the data-type, but that in the standard SQL 92 \ is not
> a special char. In PG and Mysql \ is a special char and you can escape
> single quote with back slash.
> The result is that if you try to insert a string like this (this is a test
> \' hello world) an error occur because the driver escape the single quote.
Try this simple class I wrote now. You can play around with the q2 array.
The prepared statements seem to work, with plain strings it's a mess, I
couldn't find a way to effectively insert a \ in the db. Depending from the
format, it's escaped or it throws an sql exception (moreover, being the
backslash an escape in java too, these tests tend to puzzle you after a while).
My conclusion is that we better use <cfqueryparam> from cfmx and prepared
statements from java ... (it's safer in any case I think)
import java.sql.*;
public class Pgtest {
private Connection pgConnect (String user, String passwd, String datasource)
throws ClassNotFoundException, SQLException
{
Class.forName("org.postgresql.Driver");
return DriverManager.getConnection("jdbc:postgresql:" + datasource +
"?charSet=ISO8859_1", user, passwd);
}
public static void main(String args[]) {
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt;
String q1 = "INSERT INTO mytable (name) VALUES";
String[] q2 = {
"this is a test \\' hello world", /* ok */
"this is a test \\\' hello world", /* ok */
"this is a test \' hello world", /* bum */
"\\" /* bum! */
};
Pgtest pgtest = new Pgtest();
try {
conn = pgtest.pgConnect("myuser", "mypasswd", "mydb");
// with prepared statement
pstmt = conn.prepareStatement(q1 + "(?)");
for (int i = 0; i < q2.length; i++) {
System.out.println("Prepared stmt: " + q2[i]);
pstmt.setString(1, q2[i]);
pstmt.execute();
pstmt.clearParameters();
}
stmt = conn.createStatement();
for (int i = 0; i < q2.length; i++) {
System.out.println("Plain stmt: " + q1 + "('" + q2[i] + "')");
stmt.execute(q1 + "('" + q2[i] + "')");
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
--
Rd
"Nulla � impossibile per colui che non deve farlo"
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-linux%40houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_linux or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
