preparedStatement syntax error
The solution to your problem is actually very easy, you are calling Statement.executeQuery(String) when you want to call PreparedStatement.executeQuery() -
this.stmt = con.prepareStatement(sql); // Prepares the Statement.
stmt.setInt(1, randNum); // Binds the parameter.
// return this.stmt.executeQuery(sql); // calls Statement#executeQuery
return this.stmt.executeQuery(); // calls your set-up PreparedStatement
Mysql syntax error in preparedstatement
You can't construct a preparedstatement with table name as a parameter. You need to contruct the SQL with string concatenation/placeholder with String.format. Prepared statement is for the column values not for table name.
In your case:
String query ="UPDATE `" + selectedTableVar + "` SET `" + fieldNameVar + "` = ? WHERE id = ?";
//...
preparedStmt.setString(1, "cellValue");
preparedStmt.setInt(2, id);
Also, id = ?
as @C. Helling mentioned.
As for sanitizing selectedTableVar
and fieldNameVar
variables, I can't find a link right now, but you can do the research by yourself about what is a valid qualifier in MySQL... AFAIK, any UTF character is valid, most of special symbols may be a part of a valid table name etc. Using the syntax suggested above you should bother not allowing the ` character to prevent injection and I guess that's it. But it must be investigated.
why do I get a syntax error for prepared statement?
The problem is here:
rs=preparedStatement.executeQuery(sql);
You shouldn't pass the SQL String
to executeQuery()
, since the prepared statement already contains the SQL String
with the ?
placeholder replaced by the value of movieName
.
Use:
rs=preparedStatement.executeQuery();
Java PreparedStatement SQL syntax error
Bind parameters cannot be used for identifiers in the SQL statement. Only values can supplied through bind placeholders.
This will work:
SELECT foo FROM bar WHERE id = ?
This will not work, because the table name is an identifier
SELECT foo FROM ? WHERE id = 2
You can't supply a column name, because column names are also identifiers.
A statement like this will run, but it may not do what you think it does.
SELECT ? AS foo FROM bar WHERE ? = 0
If we supply values of 'foo'
for both placeholders, the query will actually be equivalent to a query containing two string literals:
SELECT 'foo' AS foo FROM bar WHERE 'foo' = 0
MySQL will run that statement, because it's a valid statement (if the table bar exists and we have privileges on it.) That query will return every row in bar (because the predicate in the WHERE clause evaluates to TRUE, independent of the contents of the table.. And we get returned the constant string foo
.
It doesn't matter one whit that the string foo
happens to match the name of column in our table.
This restriction has to do with how the SQL optimizer operates. We don't need to delve into all the details of the steps (briefly: parsing tokens, performing syntax check, performing semantics check, determining query plan, and then the actual execution of the query plan.)
So here's the short story: The values for bind parameters are supplied too late in that process. They are not supplied until that final step, the execution of the query plan.
The optimizer needs to know which tables and columns are being referenced at earlier stages... for the semantics check, and for developing a query plan. The tables and columns have to be identified to the optimizer. Bind placeholders are "unknowns" at the time the table names and column names are needed.
(That short story isn't entirely accurate; don't take all of that as gospel. But it does explain the reason that bind parameters can't be used for identifiers, like table names and column names.)
tl;dr
Given the particular statement you're running, the only value that can be passed in as a bind parameter would be the "hashedPassword" value. Everything else in that statement has to be in the SQL string.
For example, something like this would work:
String sqltext = "SELECT * FROM mytable WHERE mycolumn = ?";
PreparedStatement prepared = connection.prepareStatement(sqltext);
prepared.setString(1, hashedPassword);
To make other parts of the SQL statement "dynamic" (like the table name and column name) you'd have to handle that in the Java code (using string concatenation.) The contents of that string would need to end up like the contents of the sqltext
string (in my example) when it's passed to the prepareStatement method.
JDBC Prepared Statement Syntax Error
reg.no.
isn't a valid column name. If you really need to use it, you should quote it:
INSERT INTO vehicle (vin, serial, make, model, year, `reg.no.`, status)
-- Here ---------------------------------------------^-------^
VALUES (?, ?, ?, ?, ?, ?, ?)";
MySQL Java prepared statement Syntax error
You are executing the query using a normal java.sql.Statement
, not using a java.sql.PreparedStatement
. This won't work because a normal Statement
does not support parameterized queries. So, remove the creation and execution of the Statement
, and make sure you execute the statement using the PreparedStatement
:
String URL = "jdbc:mysql://localhost:3306/clothing";
String USERNAME = "root";
String PASSWORD = "password";
String sql= "SELECT * FROM clothing.Lostandfound WHERE Colour = ? AND Size = ?;";
try (Connection con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement preparedStmt = con.prepareStatement(sql)) {
preparedStmt.setString(1, Data1);
preparedStmt.setString(2, Data2);
try (ResultSet rs = preparedStmt.executeQuery()) {
// process result set
}
}
Also note the addition of try-with-resources, which will ensure connections, statements and result sets are closed correctly.
2-parameter PreparedStatement is throwing syntax error, SQL State 42601, near second argument
As mentioned by Elliott, you cannot bind the table name that way.
To achieve your goal, try this:
String[] arrOfStr = arguments.split(" ", 0);
PreparedStatement preparedSelect = conn.prepareStatement("SELECT ? FROM " + arrOfStr[1]);
PreparedStatement preparedCount = conn.prepareStatement("SELECT COUNT(*) FROM " + arrOfStr[1]);
PreparedStatement - syntax error
You are missing spaces. Change your SQL to :
"INSERT INTO users " // space added
+ "(FirstName, LastName, Login, Password, AccountNumber, Ballance, Question, Answer) " // space added
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
EDIT : I missed the missing closing bracket at the end of the VALUES clause.
Related Topics
Scheduledexecutorservice Exception Handling
How to Get a Index Value from Foreach Loop in Jstl
How to Retrieve the Autoincrement Id from a Prepared Statement
Java - How to Create New Entry (Key, Value)
Java Command Line with External .Jar
Mocking Time in Java 8's Java.Time API
How to Find Out If the Value Contained in a String Is Double or Not
Karate Karate-Config.Js Not a Js Function
Put Byte Array to JSON and Vice Versa
When Does the Main Thread Stop in Java
How to Set Base Url for Rest in Spring Boot
Eclipse/Maven Error: "No Compiler Is Provided in This Environment"
Java's Collections.Shuffle Is Doing What
Apache Commons Equals/Hashcode Builder
Reading and Displaying Data from a .Txt File