0

I'm trying to use an INSERT statement to add data from a form in Spring application front end to a PostgreSQL table. The form includes mainly text values but I also include an input requirement for times in the format of HH:MM. On a side note I also include a requirement for a date in the form of DD/MM/YYYY, and the text in the form can include commas, periods, and apostrophes. Depending on the data entered into the form (e.g names, biography, addresses etc) I get the following error pointing to different positions of the data.

org.postgresql.util.PSQLException: ERROR: syntax error at or near "blank"
Position: x

Therefore I cannot pinpoint the exact root of the problem but any experience I've had with this type of error there was an issue with a symbol of some description.

I use an executeUpdate to create the insert statement in a java class like so:

int rs = stmt.executeUpdate( "INSERT INTO events VALUES 
(nextval('reviews_id_seq'::regclass), '" + eventname +" '," +description+"," 
+address+", '"+starttime+"', '"+endtime+"', 
'"+username+"','"+attendees+"');");

Any ideas?

Charles
  • 69
  • 11
  • 4
    (1) Wouldn't it be better and safer to use placeholders? SQL injection is a real thing. (2) You're better off specifying your columns: `insert into events (eventname, description, ...) values (...)`. (3) If you specify the column names then you shouldn't have to worry about manually setting the `id` to the next sequence value, the column's `default` should take care of that. – mu is too short Jul 20 '18 at 18:13

1 Answers1

1

Your statement doesn't wrap description in single quotes. There is probably a colon in the actual data you're attempting to insert. As already stated in the comments, this is a very dangerous way to compose a query. Look into using parameterized queries instead of building a query with strings.

It's also good practice to explicitly list the columns of the table in which you are inserting. Depending on your database drive, your query could look like this:

insert into events (name, description, address) values (?, ?, ?);

You then provide those values in sequential order to the database driver. (see article below).

Sql query with bind variables execution in Jdbc

What is SQL injection?

https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet

dliff
  • 116
  • 1
  • 8