From 44aecf82db1ae3fe76f16f3f92ae580130713e0e Mon Sep 17 00:00:00 2001 From: rowanbeentje Date: Wed, 24 Aug 2011 01:15:25 +0000 Subject: Rework editable row checks in views and custom query results: - Only include columns from the same database, even if they share the same table name - If a primary key was found that can be checked, use all the columns specified in the primary key - Correctly escape and format values in primary key columns - Clean up logic and string generation --- Source/SPCustomQuery.m | 52 ++++++++++++++++++++----------------------------- Source/SPTableContent.m | 51 ++++++++++++++++++++++-------------------------- 2 files changed, 44 insertions(+), 59 deletions(-) diff --git a/Source/SPCustomQuery.m b/Source/SPCustomQuery.m index 151bd681..c26ca28f 100644 --- a/Source/SPCustomQuery.m +++ b/Source/SPCustomQuery.m @@ -1815,50 +1815,44 @@ NSArray *dataRow; NSDictionary *theRow; id field; - NSString *baseString = @"WHERE ("; + NSMutableArray *argumentParts = [NSMutableArray array]; - //Look for all columns which are coming from "tableForColumn" - NSMutableArray *columnsForFieldTableName = [NSMutableArray array]; + // Check the table/view columns and select only those coming from the supplied database and table + NSMutableArray *columnsInSpecifiedTable = [NSMutableArray array]; for(field in cqColumnDefinition) { - if([[field objectForKey:@"org_table"] isEqualToString:tableForColumn]) - [columnsForFieldTableName addObject:field]; + if([[field objectForKey:@"db"] isEqualToString:database] && [[field objectForKey:@"org_table"] isEqualToString:tableForColumn]) + [columnsInSpecifiedTable addObject:field]; } - // Try to identify the field bijectively - NSMutableString *fieldIDQueryStr = [NSMutableString string]; - [fieldIDQueryStr setString:baseString]; - // --- Build WHERE clause --- dataRow = [resultData rowContentsAtIndex:rowIndex]; - // Get the primary key if there is one + // Get the primary key if there is one, using any columns present within it MCPResult *theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@.%@", [database backtickQuotedString], [tableForColumn backtickQuotedString]]]; [theResult setReturnDataAsStrings:YES]; if ([theResult numOfRows]) [theResult dataSeek:0]; + NSMutableArray *primaryColumnsInSpecifiedTable = [NSMutableArray array]; NSUInteger i; for ( i = 0 ; i < [theResult numOfRows] ; i++ ) { theRow = [theResult fetchRowAsDictionary]; if ( [[theRow objectForKey:@"Key"] isEqualToString:@"PRI"] ) { - for(field in columnsForFieldTableName) { - id aValue = [dataRow objectAtIndex:[[field objectForKey:@"datacolumnindex"] integerValue]]; + for (field in columnsInSpecifiedTable) { if([[field objectForKey:@"org_name"] isEqualToString:[theRow objectForKey:@"Field"]]) { - [fieldIDQueryStr appendFormat:@"%@.%@.%@ = %@)", - [database backtickQuotedString], - [tableForColumn backtickQuotedString], - [[theRow objectForKey:@"Field"] backtickQuotedString], - [aValue description]]; - return fieldIDQueryStr; + [primaryColumnsInSpecifiedTable addObject:field]; } } } } - // If there is no primary key, all found fields belonging to the same table are used in the argument - for(field in columnsForFieldTableName) { + // Determine whether to use the primary keys list or fall back to all fields when building the query string + NSMutableArray *columnsToQuery = [primaryColumnsInSpecifiedTable count] ? primaryColumnsInSpecifiedTable : columnsInSpecifiedTable; + + // Build up the argument + for (field in columnsToQuery) { id aValue = [dataRow objectAtIndex:[[field objectForKey:@"datacolumnindex"] integerValue]]; if ([aValue isKindOfClass:[NSNull class]] || [aValue isNSNull]) { - [fieldIDQueryStr appendFormat:@"%@ IS NULL AND ", [[field objectForKey:@"org_name"] backtickQuotedString]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@ IS NULL", [[field objectForKey:@"org_name"] backtickQuotedString]]]; } else { NSString *fieldTypeGrouping = [field objectForKey:@"typegrouping"]; @@ -1874,29 +1868,25 @@ // If the field is of type BIT then it needs a binary prefix if ([fieldTypeGrouping isEqualToString:@"bit"]) { - [fieldIDQueryStr appendFormat:@"%@=b'%@' AND ", [[field objectForKey:@"org_name"] backtickQuotedString], [aValue description]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=b'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [aValue description]]]; } else if ([fieldTypeGrouping isEqualToString:@"geometry"]) { - [fieldIDQueryStr appendFormat:@"%@=X'%@' AND ", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:[aValue data]]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=X'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:[aValue data]]]]; } // BLOB/TEXT data else if ([aValue isKindOfClass:[NSData class]]) { - [fieldIDQueryStr appendFormat:@"%@=X'%@' AND ", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:aValue]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=X'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:aValue]]]; } else { - [fieldIDQueryStr appendFormat:@"%@='%@' AND ", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareString:aValue]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@='%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareString:aValue]]]; } } } - // Remove last " AND " - if([fieldIDQueryStr length]>12) - [fieldIDQueryStr replaceCharactersInRange:NSMakeRange([fieldIDQueryStr length]-5,5) withString:@")"]; - // Check for empty strings - if (![fieldIDQueryStr length] || [fieldIDQueryStr isEqualToString:baseString]) return nil; + if (![argumentParts count]) return nil; - return fieldIDQueryStr; + return [NSString stringWithFormat:@"WHERE (%@)", [argumentParts componentsJoinedByString:@" AND "]]; } #pragma mark - diff --git a/Source/SPTableContent.m b/Source/SPTableContent.m index 7692267f..533b28c3 100644 --- a/Source/SPTableContent.m +++ b/Source/SPTableContent.m @@ -1663,49 +1663,44 @@ NSArray *dataRow; NSDictionary *theRow; id field; + NSMutableArray *argumentParts = [NSMutableArray array]; - //Look for all columns which are coming from "tableForColumn" - NSMutableArray *columnsForFieldTableName = [NSMutableArray array]; + // Check the table/view columns and select only those coming from the supplied database and table + NSMutableArray *columnsInSpecifiedTable = [NSMutableArray array]; for(field in cqColumnDefinition) { - if([[field objectForKey:@"org_table"] isEqualToString:tableForColumn]) - [columnsForFieldTableName addObject:field]; + if([[field objectForKey:@"db"] isEqualToString:database] && [[field objectForKey:@"org_table"] isEqualToString:tableForColumn]) + [columnsInSpecifiedTable addObject:field]; } - // Try to identify the field bijectively - NSMutableString *fieldIDQueryStr = [NSMutableString string]; - [fieldIDQueryStr setString:@"WHERE ("]; - // --- Build WHERE clause --- dataRow = [tableValues rowContentsAtIndex:rowIndex]; - // Get the primary key if there is one + // Get the primary key if there is one, using any columns present within it MCPResult *theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@.%@", [database backtickQuotedString], [tableForColumn backtickQuotedString]]]; [theResult setReturnDataAsStrings:YES]; if ([theResult numOfRows]) [theResult dataSeek:0]; + NSMutableArray *primaryColumnsInSpecifiedTable = [NSMutableArray array]; NSUInteger i; for ( i = 0 ; i < [theResult numOfRows] ; i++ ) { theRow = [theResult fetchRowAsDictionary]; if ( [[theRow objectForKey:@"Key"] isEqualToString:@"PRI"] ) { - for(field in columnsForFieldTableName) { - id aValue = [dataRow objectAtIndex:[[field objectForKey:@"datacolumnindex"] integerValue]]; + for (field in columnsInSpecifiedTable) { if([[field objectForKey:@"org_name"] isEqualToString:[theRow objectForKey:@"Field"]]) { - [fieldIDQueryStr appendFormat:@"%@.%@.%@ = %@)", - [database backtickQuotedString], - [tableForColumn backtickQuotedString], - [[theRow objectForKey:@"Field"] backtickQuotedString], - [aValue description]]; - return fieldIDQueryStr; + [primaryColumnsInSpecifiedTable addObject:field]; } } } } - // If there is no primary key, all found fields belonging to the same table are used in the argument - for(field in columnsForFieldTableName) { + // Determine whether to use the primary keys list or fall back to all fields when building the query string + NSMutableArray *columnsToQuery = [primaryColumnsInSpecifiedTable count] ? primaryColumnsInSpecifiedTable : columnsInSpecifiedTable; + + // Build up the argument + for (field in columnsToQuery) { id aValue = [dataRow objectAtIndex:[[field objectForKey:@"datacolumnindex"] integerValue]]; if ([aValue isKindOfClass:[NSNull class]] || [aValue isNSNull]) { - [fieldIDQueryStr appendFormat:@"%@ IS NULL AND ", [[field objectForKey:@"org_name"] backtickQuotedString]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@ IS NULL", [[field objectForKey:@"org_name"] backtickQuotedString]]]; } else { NSString *fieldTypeGrouping = [field objectForKey:@"typegrouping"]; @@ -1721,25 +1716,25 @@ // If the field is of type BIT then it needs a binary prefix if ([fieldTypeGrouping isEqualToString:@"bit"]) { - [fieldIDQueryStr appendFormat:@"%@=b'%@' AND ", [[field objectForKey:@"org_name"] backtickQuotedString], [aValue description]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=b'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [aValue description]]]; } else if ([fieldTypeGrouping isEqualToString:@"geometry"]) { - [fieldIDQueryStr appendFormat:@"%@=X'%@' AND ", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:[aValue data]]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=X'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:[aValue data]]]]; } // BLOB/TEXT data else if ([aValue isKindOfClass:[NSData class]]) { - [fieldIDQueryStr appendFormat:@"%@=X'%@' AND ", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:aValue]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=X'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:aValue]]]; } else { - [fieldIDQueryStr appendFormat:@"%@='%@' AND ", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareString:aValue]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@='%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareString:aValue]]]; } } } - // Remove last " AND " - if([fieldIDQueryStr length]>12) - [fieldIDQueryStr replaceCharactersInRange:NSMakeRange([fieldIDQueryStr length]-5,5) withString:@")"]; - return fieldIDQueryStr; + // Check for empty strings + if (![argumentParts count]) return nil; + + return [NSString stringWithFormat:@"WHERE (%@)", [argumentParts componentsJoinedByString:@" AND "]]; } /** -- cgit v1.2.3