Sqlite Query With Parameters Not Working In Java

I have a program that selects from a database given a table and column string. public void selectAllFrom(String table, String column){ String sql = 'SELECT ? FROM ?';

Solution 1:

A table or column name can't be used as a parameter to PreparedStatement. It must be hard coded.

String sql= "SELECT " +column+ " FROM " +table;

You should reconsider the design so as to make these two constant and parameterize the column values.

Solution 2:

? is a place holder to indicate a bind variable. When a SQL statement is executed, database first checks syntax, and validates the objects being referenced, columns and access permission for specified objects (i.e metadata about objects) and confirms that all are in place and valid. This stage is called parsing.

Post parsing, it substitutes bind variables to query and then proceeds for actual fetch of results.

Bind variables can be substituted in any place in query to replace an actual hard coded data/strings, but not the query constructs them selves. It means

• You can not use bind variables for keywords of sql query (ex: SELECT, UPDATE etc.)
• You can not use bind variables for objects or their attributes (i.e table names, column names, functions, procedures etc.)
• You can use them only in place of a otherwise hard coded data.

ex: SELECT FIRST_NAME, LAST_NAME, 'N' IS_DELETED FROM USER_DATA WHERE COUNTRY ='CANADA' AND VERIFIED_USER='YES' In above sample query, 'N','CANADA' and 'YES' are the only strings which can be replaced by a bind variable, not any other word.

Using bind variable is best practice of coding. It improves query performance (when used with large no. of queries in tuned database products like Oracle or MSSQL) and also protects your code against sql injection attacks.

Constructing query by concatenating strings (especially data part of query) is never recommended way. You can still construct a query by concatenation for other parts like table name or column name as long as those strings are not directly taken from input.

Below example is acceptable:

query = "Select transaction_id, transaction_date from ";
if (isHistorical(reportType) 
   { query = query + "HISTORY_TRANSACTIONS" ;}
else
   {query = query + "PRESENT_TRANSACTIONS" ; }

recommended practice is to use

Stringquery_present="SELECT transaction_id, transaction_date from PRESENT_TRANSACTIONS";
Stringquery_historical="SELECT transaction_id, transaction_date from HISTORY_TRANSACTIONS";

if (isHisotrical(reportType))
 { 
    ps.executeQuery(query_historical);
 }else{
    ps.executeQuery(query_present);
 }