Can't get the SQL query to work with variables b/c of syntax errors

Question

 String query = "insert into course_data values(null," + CourseName + ","
            + SCrsDesrpTemp + "," + CrsDes + "," + crsurl + "," 
                    + youtube + "," + sqlStrDate + "," + crsduration + "," 
            + CrsImg + "," + "'Open2Study', 'Free', 'English', 'Yes'," + CrsImgUni + date + ")";

I keep getting syntax errors. The variable names are strings that hold values from scraped websites. I printed them out and they work fine, they all are of type string. But for some reason, I keep getting syntax error in the SQL query.

If you use parameterized queries, you won't have this problem. — Gordon Linoff, Nov 10 '15 at 21:09
I suspect the issue is with quotes as @Turophile suggests. Especially because you are using single quotes inside of a string defined using double quotes. I believe the proper way would be to use single quotes and identify quotes within strings using `''` — ander2ed, Nov 10 '15 at 21:12
When debugging a problem like this, it is not enough to print out the values to check them. You should also print/capture the value of `query` before it is sent to the database and then run it "manually", looking for possible errors. — Turophile, Nov 10 '15 at 21:14
In MySQL, would it also be necessary to have a semi-colon as part of the query following the `)` on `values`, or is that not required? — ander2ed, Nov 10 '15 at 21:18

score 0 · Accepted Answer · edited May 23 '17 at 11:59

0

When presented to the database like this, string (and date) values need to be in single quotes.

String query = "insert into course_data values(null,'" + CourseName + "','"
            + SCrsDesrpTemp + "','" + CrsDes + "','" + crsurl + "','" 
                    + youtube + "','" + sqlStrDate + "','" + crsduration + "','" 
            + CrsImg + "'," + "'Open2Study', 'Free', 'English', 'Yes','" + CrsImgUni + date + "')";

The last part may be incorrect "CrsImgUni + date" and you may need to ensure that dates are formatted correctly.

See also What is SQL injection?

edited May 23 '17 at 11:59

Community

1
1

answered Nov 10 '15 at 21:11

Turophile

3,157
1
10
21

I am getting this error now: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Open2Study', 'Free', 'English', 'Yes','https://www.open2study.com/sites/default/' at line 1 – StephCurry3093 Nov 10 '15 at 21:18
Extra single quote there. Try my advice about running the query yourself. Answer edited. – Turophile Nov 10 '15 at 21:21
I am a little confused. Where is the extra single quote? On the "')"? – StephCurry3093 Nov 10 '15 at 21:36
The extra quote was this one (now removed): `+ CrsImg + "',>>>'<< – Turophile Nov 10 '15 at 22:44
Why is that considered an extra quote when all the other commas have two single quotes for each one of them – StephCurry3093 Nov 10 '15 at 23:12
Because in the other places the quotes are going around a value from a variable, what follows this comma is a string constant `"'Open2Study',...` which provides it's own quotes thus no need to add one. Again, this is clearer if you capture the value of `query` and look at it. – Turophile Nov 10 '15 at 23:24

Can't get the SQL query to work with variables b/c of syntax errors

1 Answers1