org.h2.jdbc.JdbcSQLSyntaxErrorException h2 database java-CodePudding

Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO SMTP_DATA(SMTP_SERVER, SMTP_USERNAME, SMTP_PASSWORD, SMTP_FROM, SMTP_TO) VALUES (DEMO.STMP.COM, DEMOUSERNAME, DEMOPASSWORD, FROMDEMO@[*]MAIL.COM, [email protected]);"; expected "(, ., [, ::, AT, FORMAT, *, /, %, , -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:

I am kind new to h2 and sql statments I try to insert values to table

INSERT  INTO smtp_data(smtp_server, smtp_username, smtp_password, smtp_from, smtp_to) VALUES (demo.stmp.com, demousername, demopassword, fromdemo@mail.com, todemo@mail.com);

I think the commas make me a problem in the values how to format corect to don't have this exception.

This is the table:

create table if not exists smtp_data(id int primary key auto_increment, smtp_server varchar(30) not null, smtp_username varchar(30) not null, smtp_password varchar(40) not null, smtp_from varchar(20) not null, smtp_to varchar(20) not null

CodePudding user response：

String literals need to be in ' symbols. So, whenever you write a table or column name, just write it. But when you write actual data, use ': INSERT INTO smtp_data(smtp_server) VALUES ('put this stuff in quotes');

HOWEVER

This should NEVER come up. The problem is, usually input is not exactly 'safe'. Some user entered it someplace (and who knows what malicious intent they might have), or somebody who doesn't know the exact process flow of this app did it. Even if currently you don't think that can happen, software has the nasty tendency to be used for things that you didn't imagine it would be used for when you write it. Hence, this is a huge security disaster waiting to happen. After all, what if somebody tries, for funsies, this SMTP server:

haha hacked your box'); DROP TABLE smtp_data CASCADE; SHELL_EXEC 'FORMAT C: /y /force'; --

The day someone does that is going to be a very, very bad day for you.

The solution is something called PreparedStatement, where instead of text values like 'smtp.server.com', you just put an unquoted question mark: INSERT INTO smtp_data(field1, field2) VALUES (?, ?); - that's the SQL you pass to PreparedStatement. Then separately you 'set' the value for each question mark:

ps.setString(1, "smtp.server.com");

This roundabout way means that if some clown tries smtp.server.com'; hack the things it simply won't work. Everything is safely escaped by the DB engine itself, which is the only one that can reliably do this.

You need to be doing it this way, or you're going to write a security leak. Not a matter of if, but when.

CodePudding user response：

I think you are missing to enclose your value into single quotes, check here, https://www.codegrepper.com/code-examples/sql/h2 insert data on startup