In Clause and Placeholders

IN clause and placeholders

A string of the form "?, ?, ..., ?" can be a dynamically created string and safely put into the original SQL query (because it is a restricted form that does not contain external data) and then the placeholders can be used as normal.

Consider a function String makePlaceholders(int len) which returns len question-marks separated with commas, then:

String[] names = { "name1", "name2" }; // do whatever is needed first
String query = "SELECT * FROM table"
    + " WHERE name IN (" + makePlaceholders(names.length) + ")";
Cursor cursor = mDb.rawQuery(query, names);

Just make sure to pass exactly as many values as places. The default maximum limit of host parameters in SQLite is 999 - at least in a normal build, not sure about Android :)

---

Here is one implementation:

String makePlaceholders(int len) {
    if (len < 1) {
        // It will lead to an invalid query anyway ..
        throw new RuntimeException("No placeholders");
    } else {
        StringBuilder sb = new StringBuilder(len * 2 - 1);
        sb.append("?");
        for (int i = 1; i < len; i++) {
            sb.append(",?");
        }
        return sb.toString();
    }
}

How to use placeholders with in (...) clause?

Posting comment as answer, as requested.

Generate your own placeholder string. Like so:

my @nums = (22,23); 
my $placeholder = join ",", ("?") x @nums; 
$query->execute(@nums); 

How to use placeholders in like clause in SQL query?

Query Should be like this::

    PreparedStatement pstmt = con.prepareStatement ("Select * 
    from 
    Table1 where Identifier = 'ABC' and Identifier_Type  like?");

While setting in prepared statement:

   pstmt.setString(1, "%" + data + "%"); // where data is string Variable

OR By using SQL function

   PreparedStatement pstmt = con.prepareStatement(
  "select * from Table1 where Identifier = 'ABC' and Identifier_Type  
    like  CONCAT( '%',?,'%')";
     
     pstmt.setString(1, data); // Where data is String variable

How to use placeholders at IN() clause when there is other placeholders?

Ok, as no one bothered to provide an example. Like aynber said, you need to merge arrays. So the following code should be fine:

$stmt->execute(array_merge([$part_of_tag,$part_of_tag], $selected_tags_arr));

there could be a problem though, if, like you said, $selected_tags_arr could be empty. It will cause a syntax error.

The remedy depends on the result you want. if you want that your query finds nothing if array is empty, then change your code like this:

$in  = $selected_tags_arr ? str_repeat('?,', count($selected_tags_arr) - 1) . '?' : 'NULL';

PDO prepared statements IN clause with named placeholders doesn't work as expected

This should work for you:

So as already said in the comments you need a placeholder for each value which you want to bind into the IN clause.

Here I create first the array $ids which only holds the plain ids, e.g.

[2, 3]

Then I also created the array $preparedIds which holds the placeholders as array, which you then later use in the prepared statement. This array looks something like this:

[":id2", ":id3"]

And I also create an array called $preparedValues which holds the $preparedIds as keys and $ids as values, which you then later can use for the execute() call. The array look something like this:

[":id2" => 2, ":id3" => 3]

After this you are good to go. In the prepared statement I just implode() the $preparedIds array, so that the SQL statement look something like this:

... IN(:id2,:id3) ...

And then you can simply execute() your query. There I just array_merge() your $preparedValues array with the other placeholders array.

<?php

    $ids = array_map(function($item){
        return $item->id;
    }, $entitlementsVOs);

    $preparedIds = array_map(function($v){
        return ":id$v";
    }, $ids);

    $preparedValues = array_combine($preparedIds, $ids);


    $timestart = (!empty($_GET['start']) ? $_GET['start'] : NULL );
    $timeend = (!empty($_GET['end']) ? $_GET['end'] : NULL );


    $statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(" . implode(",", $preparedIds) . ") AND timestart >= :timestart AND timestart + timeduration <= :timeend");
    $statement->setFetchMode(\PDO::FETCH_CLASS, get_class(new EventVO()));

    if($statement->execute(array_merge($preparedValues, ["timestart" => $timestart, "timeend" => $timeend]))) {
        return $statement->fetchAll();
    } else {
        return null;
    }

?>

Also I think you want to put an if statement around your query, since your query will not run if the values of $timestart and $timeend are NULL.

Mysql named placeholders in python used in the IN clause

MySQLdb already handles the escaping of a sequence for you:

>>> con = MySQLdb.connect(db='test')
>>> con.literal([1,2,3])
('1', '2', '3')
>>> cur = con.cursor()
>>> cur.execute("select * from test where id in %(foo)s", {'foo': [1,2,3]})
3L
>>> cur._executed
"select * from test where id in ('1', '2', '3')"

So by removing the parenthesis around your placeholder it should work - but only for sequences with more then one element, because a single element is formatted like this:

>>> con.literal([1])
('1',)

Inserted into a SQL query, the trailing comma makes it illegal SQL.

To work arount this, you could also define your own converter to convert a custom type to the representation you like:

import MySQLdb.converters
conv = MySQLdb.converters.conversions.copy()

class CustomList(list):
    def __init__(self, *items):
        super(CustomList, self).__init__(items)

conv[CustomList] = lambda lst, conv: "(%s)" % ', '.join(str(item) for item in lst)

con = MySQLdb.connect(db='test', conv=conv)
cur = con.cursor()
cur.execute('select * from test where id in %(foo)s', {'foo': CustomList(0, 1, 2)})
print cur._executed

select * from test where id in (0, 1, 2)

This way the quotes around the list items are gone.

It would also work to just replace the converter for list, but that would change the behaviour for all lists and possibly introduce vulnerabilities. The above way of formatting a list would not be safe for a list containing strings, as it doesn't escape special characters.

To do that, you would have to recursively escape all items in the list:

>>> ...
>>> conv[list] = lambda lst, cv: "(%s)" % ', '.join(cv[type(item)](item, cv) for item in lst)
>>> con = MySQLdb.connect(..., conv=conv)
>>> con.literal([1, "it's working...", 2])
"(1, 'it\\'s working...', 2)"

How to pass a list to an IN clause via a placeholder with Ruby Sequel

query = "
SELECT *
FROM PRICE
WHERE REGION IN ?"
regions = Sequel.lit("'NSW1', 'VIC1'")
sql = db.fetch(query, regions).sql
puts "sql: #{sql}"

Gives:

sql: SELECT *
FROM PRICE
WHERE REGION IN ('NSW1', 'VIC1')

which is what we want.

Extended placeholders for SQL, e.g. WHERE id IN (??)

You might want to avoid using := as a placeholder because it already has a usage in for example MySQL.

See for example this answer for a real world usage.

---

Related Topics