Having a Column Name as Input Parameter of a Preparedstatement

Having a Column name as Input Parameter of a PreparedStatement

No, JDBC does not allow this. Only column values can be set. If you want to make dynamic changes to the sql statement you will have to do it before you create the PreparedStatement.

PreparedStatement: Can I supply the column name as parameter?

you could code up a a set of sql queries and store them in a map, then grab one based on the column in question.

enum column { a, b, c}

Map<column, string> str;

static {
 str.put(a, "select * from tbl where a = ? ");
 ...
}

then just grab one out of the map later based on the enum. String appends in sql statements have a way of becoming security problems in the future.

Variable column names using prepared statements

This indicates a bad DB design. The user shouldn't need to know about the column names. Create a real DB column which holds those "column names" and store the data along it instead.

And any way, no, you cannot set column names as PreparedStatement values. You can only set column values as PreparedStatement values

If you'd like to continue in this direction, you need to sanitize the column names (to avoid SQL Injection) and concatenate/build the SQL string yourself. Quote the separate column names and use String#replace() to escape the same quote inside the column name.

Getting column name instead of values in prepared statement

This is not possible, with your way so to solve your problem.

Change your code like this :

String att = "FIRST_NAME";

string q="select " + att + " from EMPLOYEE where salary>?";

Preparedstatement pst=connectionobject.preparedstatement(q);
pst.setint(1,10000); 

IF YOU AFRAID THAT THE USER CAN MAKE AN SQL Injection than use this solution

You should to check if your column exist in your table or not :

SELECT * 
    FROM information_schema.COLUMNS 
    WHERE 
        TABLE_SCHEMA = 'db_name' 
    AND TABLE_NAME = 'table_name' 
    AND COLUMN_NAME = 'column_name'

Like so :

public boolean column_exist(String att) {
    boolean succes = false;
    CreerConnection con = new CreerConnection();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultat = null;
    try {
        connection = con.getConnection();

        statement = connection.prepareStatement("SELECT * \n"
                + "FROM information_schema.COLUMNS "
                + " WHERE"
                + " TABLE_SCHEMA = 'db_name'"
                + " AND TABLE_NAME = 'table_name'"
                + " AND COLUMN_NAME = ?");
        statement.setString(1, att);
        resultat = statement.executeQuery();

        if (resultat.next()) {
            succes = true;
        }

    } catch (SQLException e) {
        System.out.println("Exception = " + e);
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
            }
        }
    }
    return succes;
}

If the column exist then you can continue like so:

if(column_exist(att)){
   string q="select " + att + " from EMPLOYEE where salary>?";

   Preparedstatement pst=connectionobject.preparedstatement(q);
   pst.setint(1,10000); 
}

Learn more here :

MySQL, Check if a column exists in a table with SQL

Hope this can help you.

Can PHP PDO Statements accept the table or column name as parameter?

Table and Column names CANNOT be replaced by parameters in PDO.

In that case you will simply want to filter and sanitize the data manually. One way to do this is to pass in shorthand parameters to the function that will execute the query dynamically and then use a switch() statement to create a white list of valid values to be used for the table name or column name. That way no user input ever goes directly into the query. So for example:

function buildQuery( $get_var ) 
{
    switch($get_var)
    {
        case 1:
            $tbl = 'users';
            break;
    }

    $sql = "SELECT * FROM $tbl";
}

By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.

MySql prepare statement - is it possible to parametrize column name or function name?

You cannot parametrize column/table/function name/alias. As, PREPARE statement only allow "values" part of the SQL query to be used as parameters. Function/Table/Column name/alias are used to determine the validity of the SQL statement; and thus cannot be changed during run-time execution. Changing it at execution time would potentially alter whether the SQL statement was valid.

You can think of it as compiling a code; hence the compiler must know all the function/class name(s) etc for creating a valid executable (yes, we can do dynamic classes, but that is rare). On the other hand, we can change input "values" to the program, but generally cannot change the operations to be done on the input data.

Also, MySQL server would consider the parameters as literals, and apply quotes around them, before using them in query execution.

Now, in your case, you can still use the function name as parameter for Stored procedure, and generate the query string using that. But you cannot use it as a parameter for the query itself.

delimiter $$
create procedure test(in func varchar(20), in col varchar(20))
  begin

    set @c = col;

    -- use concat function to generate the query string using func parameter
    set @sql = concat('select ', func, '(?) from table');

    -- prepare the statement
    prepare stmt from @sql;

    -- execute
    execute x using @c;

    -- don't forget to deallocate the prepared statement
    deallocate prepare stmt;
  end$$
delimiter ;

Mysqli prepared statement column with variable

It's impossible to use a parameter for a column or a table name. Instead, they must be explicitly filtered out before use, using a white list approach.

// define a "white list"
$allowed = ['Node5', 'Node4'];

// Check the input variable against it 
if (!in_array($server, $allowed)) {
    throw new Exception("Invalid column name");
}

// now $server could be used in the SQL string
$sqlString = "UPDATE staff_members SET $server=? WHERE Username=?";
$stmt = $connection->prepare($sqlString);
$stmt->bind_param("ss", $rank, $username);
$stmt->execute();

Can I parameterize the table name in a prepared statement?

Short answer to your question is "no".

In the strictest sense, at the database level, prepared statements only allow parameters to be bound for "values" bits of the SQL statement.

One way of thinking of this is "things that can be substituted at runtime execution of the statement without altering its meaning". The table name(s) is not one of those runtime values, as it determines the validity of the SQL statement itself (ie, what column names are valid) and changing it at execution time would potentially alter whether the SQL statement was valid.

At a slightly higher level, even in database interfaces that emulate prepared statement parameter substitution rather than actually send prepared statements to the database, such as PDO, which could conceivably allow you to use a placeholder anywhere (since the placeholder gets replaced before being sent to the database in those systems), the value of the table placeholder would be a string, and enclosed as such within the SQL sent to the database, so SELECT * FROM ? with mytable as the param would actually end up sending SELECT * FROM 'mytable' to the database, which is invalid SQL.

Your best bet is just to continue with

SELECT * FROM {$mytable}

but you absolutely should have a white-list of tables that you check against first if that $mytable is coming from user input.

How to use a tablename variable for a java prepared statement insert

You can't. You need to contruct the sql with string concatenation/placeholder with String.format. prepared statement is for the column values not for table name.

---

Related Topics