SQL Syntax Error Exception

Question

I am trying to execute the following statement:

String sql = "INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) VALUES (" + a.getImgURL() + " , " + a.getLinkURL() + " , " + a.getClient() + " , " + a.getIdx() + ");";

But I am getting an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'fooUrl' in 'field list'

But fooUrl is one of the values - it is the result of calling a.getImgUrl().

I'm new to working with databases and I'm sure that a simple syntactical error is causing this.

Answer 1

You will avoid these kind of problems and you will write safer code if you use a prepared statement.

String sql = "INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) VALUES (?, ?, ?, ?)";
final PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, a.getImgURL()) ;
statement.setString(2, a.getLinkURL()) ;
statement.setString(3, a.getClient()) ;
statement.setString(4, a.getIdx());
final ResultSet results = statement.executeQuery();

Answer 2

You are missing quotes around your data. The way you wrote it, you are referring to column names.

Rewrite like this:

String sql = "INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) 
 VALUES ('" + a.getImgURL() + "' , '" + a.getLinkURL() + "' , '" +
 a.getClient() + "' , '" + a.getIdx() + "');";

Obviously, you will have to escape the data before you put it into the database.

Answer 3

Surround any string values with double quotes. You'll need to escape them too.

String sql = "INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) VALUES (\"" + a.getImgURL() + "\" , "\" + a.getLinkURL() + \"" , \"" + a.getClient() + "\" , " + a.getIdx() + ");"; 

Obligatory don't forget to sanitize your input for single quotes remark.

Answer 4

It's hard to try to debug SQL by looking at code that builds SQL.

String sql = "INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) VALUES ("
 + a.getImgURL() + " , " + a.getLinkURL() + " , " + a.getClient() + " , " 
 + a.getIdx() + ");";

Instead, inspect the resulting sql string:

INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) VALUES (fooURL, ...);

You should be able to see more easily when you look at the SQL that your fooURL has no quotes around it. Therefore it's interpreted as an expression, not a simple scalar string value. An expression of a single word is just assumed to be a column name, but no such column exists by that name in the ad table.

It would be far better to avoid interpolating strings into your SQL statement. Learn how to use prepared queries and query parameters instead.

String sql = "INSERT INTO `ad`.`ad` (`imgURL`, `linkURL`, `client`, `idx`) "
 + " VALUES (?, ?, ?, ?)";
PreparedStatement insertAd = con.prepareStatement(sql);
insertAd.setString(1, a.getImgUrl);
insertAd.setString(2, a.getLinkUrl);
insertAd.setInt(3, a.getClient);
insertAd.setInt(4, a.getIdx);
insertAd.executeUpdate();

You don't need to quote or escape query parameters. It's far easier to use parameters than to remember where your balanced quote marks are. And for that reason, it's more secure too, because you're more likely to do it right.