Best way to pass array of integers to sqlite for an 'IN' clause

Question

I'm implementing an image dao that selects images based on a client_type (eg dropbox, facebook, etc), as follows:

Return results from any enabled client matching the supplied list of client types:

//clientTypes is an array of NSNumber objects - there's an enum for each client type
- (NSArray*)listByClientTypes:(NSArray*)clientTypes
{
    __block NSArray* results;

    [[self.manager queue] inDatabase:^(FMDatabase* database)
    {
        NSMutableArray* mappedResults = [[NSMutableArray alloc] init];
        NSString* sql = @"SELECT IMAGE_ADDRESS, CLIENT_TYPE, CREATED, FK_LOCATIONS_UNIQUE_COORD, PLACE_NAME, CITY, COUNTRY, IMAGE "
            "FROM IMAGES LEFT JOIN LOCATIONS ON IMAGES.FK_LOCATIONS_UNIQUE_COORD = LOCATIONS.UNIQUE_COORD "
            "WHERE CLIENT_TYPE IN (SELECT CLIENT_TYPE FROM CLIENT_STATE WHERE ENABLED = 1) "
            "AND CLIENT_TYPE IN (%@)"; // <------- THIS IS THE LIST OF CLIENTS
        FMResultSet* resultSet = [database executeQuery:sql];
        while ([resultSet next])
        {
            [mappedResults addObject:[self extractImageFrom:resultSet]];
        }
        results = [mappedResults copy];
    }];
    return results;
}

What's the best way to pass the 'IN' clause?

Normally I would use parameter binding (eg '?'), but I don't know how to do this for an unknown number of parameters. Is this possible?

For want of a better way, I was planning on just substituting a comma-separating list into the SQL query. . this would be quite safe given they're just integers.

Have a look at this relation question: [Efficiently create placeholder template NSString with NSArray of values](http://stackoverflow.com/questions/19384072/efficiently-create-placeholder-template-nsstring-with-nsarray-of-values). — Martin R, Oct 30 '13 at 12:39
w00t! Thanks @MartinR . . . do you think I should delete this question? — Jasper Blues, Oct 30 '13 at 12:40

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

If you're using a non-integer data-type its important to use SQL binding, as described in @MartinR's answer here. This is to:

Avoid problems with quotes an SQL that isn't properly escaped.
Avoid SQL-injection vulnerabilities.

Given, that in your case you have a list of integers, you could try something like this:

NSString *sql =
[NSString stringWithFormat:@"SELECT IMAGE_ADDRESS, CLIENT_TYPE, CREATED, FK_LOCATIONS_UNIQUE_COORD, PLACE_NAME, CITY, COUNTRY, IMAGE "
            "FROM IMAGES LEFT JOIN LOCATIONS ON IMAGES.FK_LOCATIONS_UNIQUE_COORD = LOCATIONS.UNIQUE_COORD "
            "WHERE CLIENT_TYPE IN (SELECT CLIENT_TYPE FROM CLIENT_STATE WHERE ENABLED = 1) "
            "AND CLIENT_TYPE IN (%@)",[clientTypes componentsJoinedByString:@"','"]];

edited May 23 '17 at 12:05

Community

1
1

answered Oct 30 '13 at 12:40

woz

10,888
3
34
64

Thanks. . . I was thinking something like this will do. . . MartinR also gave me a link to a way of making it a list of SQL parameters. . . is there any harm in doing it this way? I can't think of anything that will cause the end of civilization as we know it. – Jasper Blues Oct 30 '13 at 12:42
1

I saw that, but I think `componentsJoinedByString` is a lot easier to read. – woz Oct 30 '13 at 12:43
1

The disadvantage of this solution might be that is does not *bind* the arguments to placeholders in a prepared statement. If you have just an array of integers then there will be no problem. But if any string contains parentheses, quotation marks or similar, then you are vulnerable to a SQL injection attack ... This: http://xkcd.com/327/ is my standard reference for the problem. – Martin R Oct 30 '13 at 12:45
I will definitely use @MartinR's technique in future (not so much for the avoidance of SQL-injection, but to handle non-escaped characters properly). . given I have a list of numbers in this case, I'll just short-cut it. – Jasper Blues Oct 30 '13 at 12:48
@MartinR I strongly agree, but assuming integers are only allowed, the shortcut isn't terrible. Also, the database isn't running on a web server, and being intentionally malicious to your own device doesn't make sense. – woz Oct 30 '13 at 12:51

Best way to pass array of integers to sqlite for an 'IN' clause

1 Answers1