[Amazon](500310) Invalid operation: syntax error at or near "S" RedShift Error

2
votes

I and my team are currently experiencing this problem when we are executing, our Lambda function to insert our json data into Redshift from Amazon S3. We continue to get this error

java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at or near "S"

This is our code we are executing to run the query, which is being built into a String Builder. 

sql.append("Insert into comparison values ('" + d_timestamp + "','" + d_category + "','" + d_modification_method 
+ "','" + d_match + "','" + d_operator_name + "','" 
+ d_operator_email + "','" + d_datpoint + "','" + d_datapointcontent + "');");

It seems that all the other SQL commands are fine, before we encounter this. Have no idea where this error is and what it is for. Would appreciate any help

1
javalambdaamazon-redshift

1 Answers

5
votes

Most likely you are hitting data that is escaping out of the single quotes. Don't create sql statements by appending strings, you'll run into this problem, you'll be open to SQL Injection attacks, and a handful of other issues I'm too lazy to look up right now :p

You're best off constructing a PreparedStatement: https://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/

Your anecdote of the day: Use PreparedStatements 100% of the time, all the time.