Hello,
I'm not sure if PreparedStatement parameter insertion is actually
supposed to work with the AS clause in CREATE VIEW to begin with, but
when I tried it, no error was thrown so I assumed it should work.
Anyway, it doesn't, it just produces an empty result set when trying
to query the view later. My use case for this is dynamically creating
a view in code, using potentially unsafe parameters. User-supplied
queries are then analysed, and parts rewritten to include the view
(which provides a subset of data) instead of the original table (which
provides all the data, including data the user shouldn't have access
to).
Here is a test case to reproduce this:
public class H2Test {
public static void main(String[] args) throws Exception {
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
Statement s = conn.createStatement();
s.execute("CREATE TABLE Test(id INT AUTO_INCREMENT NOT NULL,
f1 VARCHAR NOT NULL, f2 VARCHAR NOT NULL)");
s.execute("INSERT INTO Test(f1, f2) VALUES ('value1','value2')");
s.execute("INSERT INTO Test(f1, f2) VALUES ('value1','value3')");
s.execute("CREATE VIEW Test_View AS SELECT f2 FROM Test WHERE
f1='value1'");
ResultSet rs = s.executeQuery("SELECT * FROM Test_View");
System.out.println("(not using prepared statement) Expect to
see 'value2' and 'value3' printed below.");
while (rs.next()) {
String str = rs.getString("f2");
System.out.println(str);
}
s.execute("DROP VIEW Test_View");
PreparedStatement ps = conn.prepareStatement("CREATE VIEW
Test_View AS SELECT f2 FROM Test WHERE f1=?");
ps.setString(1, "value1");
ps.executeUpdate();
rs = s.executeQuery("SELECT * FROM Test_View");
System.out.println("(using prepared statement) Expect to see
'value2' and 'value3' printed below (or at least an error thrown)");
while (rs.next()) {
String str = rs.getString("f2");
System.out.println(str);
}
}
}
Erin
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
