Re: [h2] Alter table not working from code with prepared statements, but working from console (browser)
Parameterized placeholders in prepared statements can only be used for
values, not for database objects such as table names or column names.
You can use string concate but please verify the column name before adding
or it might lead to sql injection.
PreparedStatement preparedStatement = null;
try { String columnName = User.getUsername();
if (!columnName.matches("[A-Za-z0-9_]+")) { // something like this
throw new IllegalArgumentException("Invalid column name: " +
columnName);
}
final String QUERY_SQL = "ALTER TABLE TableName ADD " + columnName + "
BOOLEAN";
preparedStatement = connection.prepareStatement(QUERY_SQL);
preparedStatement.executeUpdate();
preparedStatement.close();
} catch (SQLException e) {
On Friday, April 14, 2023 at 8:20:07 PM UTC+5:30 Evgenij Ryazanov wrote:
> Hello!
>
> JDBC parameters can only be used to specify values (literals). They cannot
> be used to specify identifiers, identifiers (including names of columns)
> aren't parameterizable.
>
> Also your decision to create an own column for each user looks like a bad
> idea, usually you shouldn't store data in that way in relational databases.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/d450a325-e4c5-4382-99fe-f48ba6ef5ffbn%40googlegroups.com.
Re: [h2] Alter table not working from code with prepared statements, but working from console (browser)
Hello! JDBC parameters can only be used to specify values (literals). They cannot be used to specify identifiers, identifiers (including names of columns) aren't parameterizable. Also your decision to create an own column for each user looks like a bad idea, usually you shouldn't store data in that way in relational databases. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/437ce411-0a4f-4003-98bf-3927e7df04a6n%40googlegroups.com.
Re: [h2] Alter table not working from code with prepared statements, but working from console (browser)
Greetings.
To my best knowledger, you can use Parameters only for QUERIES and DML,
but not for DDL statements.
Although you can have a look
at https://github.com/manticore-projects/MJdbcUtils which I wrote
exactly for this kind of challenges.
It rewrites your parameterised SQL Statement and I used it for ORACLE's
CREATE TABLE ... AS SELECT ... FROM (where the SELECT must not contain
parameters).
Good luck and cheers
Andreas
On Fri, 2023-04-14 at 04:44 -0700, airjairj wrote:
> The following code is what i'm using for the alter table, nothing
> special but it results in an exception:
> Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in
> SQL statement "ALTER TABLE TableName ADD [*]? BOOLEAN"; expected
> "identifier"; SQL statement:
> ALTER TABLE TableName ADD ? BOOLEAN [42001-214]
>
> The code:
> PreparedStatement preparedStatement = null;
> try {
> final String QUERY_SQL = "ALTER TABLE TableName ADD ? BOOLEAN";
> preparedStatement = connection.prepareStatement(QUERY_SQL);
> preparedStatement.setString(1, User.getUsername());
> preparedStatement.executeUpdate();
> preparedStatement.close();
> } catch (SQLException e) {
> ...
>
>
> For context:
> I'm trying to add a colum (of boolean) to the table named like the
> user and it works if i copy and paste the instruction on the browser
> and run it, am i missing something?
> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/c7651d96-15fb-42e2-b4c1-bdee0ac1f2dan%40googlegroups.com
> .
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/7190f68f2ec4abe866f54d833003b416d6195d11.camel%40manticore-projects.com.
[h2] Alter table not working from code with prepared statements, but working from console (browser)
The following code is what i'm using for the alter table, nothing special
but it results in an exception:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL
statement "ALTER TABLE TableName ADD [*]? BOOLEAN"; expected "identifier";
SQL statement:
ALTER TABLE TableName ADD ? BOOLEAN [42001-214]
The code:
PreparedStatement preparedStatement = null;
try {
final String QUERY_SQL = "ALTER TABLE TableName ADD ? BOOLEAN";
preparedStatement = connection.prepareStatement(QUERY_SQL);
preparedStatement.setString(1, User.getUsername());
preparedStatement.executeUpdate();
preparedStatement.close();
} catch (SQLException e) {
...
For context:
I'm trying to add a colum (of boolean) to the table named like the user and
it works if i copy and paste the instruction on the browser and run it, am
i missing something?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/c7651d96-15fb-42e2-b4c1-bdee0ac1f2dan%40googlegroups.com.
