Error with a simple parameterized query - Java / SQL

Question

Following one of my previous questions related to the design of the method , I was recommended to implement my SQL queries as a parameterized query, rather than a simple string.

I never used parameterized queries before I decided to start with something simple, do the following Select :

String select = "SELECT * FROM ? "; PreparedStatement ps = connection.prepareStatement(select); ps.setString(1, "person"); 

This gives me the following error: "[SQLITE_ERROR] SQL error or missing database (about"? ": Syntax error)

Then I tried a modified version with additional criteria:

 String select = "SELECT id FROM person WHERE name = ? "; PreparedStatement ps = connection.prepareStatement(select); ps.setString(1, "Yui"); 

This version works fine, in my first example, am I missing the point of parameterized queries or am I creating them incorrectly?

Thanks!

Answer 1

Simply put, SQL bindings cannot join tables, only where are the values ​​of the sentence. There are some technical reasons associated with the "compilation" of prepared SQL statements. In general, parameterized queries were designed to make SQL more secure by preventing SQL injection, and this had the side advantage of making queries more “modular”, but not so much as to be able to dynamically set the table name (because it assumes you already know which table will be).

Answer 2

If you need all the rows from the PERSON table, here is what you should do:

 String select = "SELECT * FROM person"; PreparedStatement ps = connection.prepareStatement(select); 

Variable binding does not dynamically bind table names, as mentioned above. If you have a table name included in your method as a variable, you can build the entire query, as shown below:

 String select = "SELECT * FROM " + varTableName; PreparedStatement ps = connection.prepareStatement(select); 

Parameterized queries are for querying field names, not table names!

Answer 3

Prepared statements are still SQL and should be built with the appropriate where clause; those. where x = y. One of their advantages is that they are analyzed by RDMS at the first scan, and not every time they are sent, which speeds up subsequent execution of the same request with different binding values.