From a23bb9169015b21191bf072a7da6c668991907df Mon Sep 17 00:00:00 2001 From: jakob Date: Tue, 24 Mar 2009 18:43:58 +0000 Subject: - fixed issue #203 (backticks in identifiers not supported) - added a backtickQuotedString: method to SPStringAdditions - created the file SPArrayAdditions for a componentsJoinedAndBacktickQuoted: method In the future, we should use backtickQuotedString: to quote identifiers like this: [NSString stringWithFormat:@"SELECT * FROM %@", [tableName backtickQuotedString]] --- Source/SPArrayAdditions.h | 29 +++++++++++++++++++ Source/SPArrayAdditions.m | 43 +++++++++++++++++++++++++++ Source/SPStringAdditions.h | 2 ++ Source/SPStringAdditions.m | 12 ++++++++ Source/SPTableData.m | 29 +++++++++++++++---- Source/TableContent.m | 72 ++++++++++++++++++++++++---------------------- Source/TableDocument.m | 23 ++++++++------- Source/TableDump.m | 24 +++++++++------- Source/TableSource.m | 60 +++++++++++++++++++------------------- Source/TablesList.m | 24 +++++++++------- 10 files changed, 217 insertions(+), 101 deletions(-) create mode 100644 Source/SPArrayAdditions.h create mode 100644 Source/SPArrayAdditions.m (limited to 'Source') diff --git a/Source/SPArrayAdditions.h b/Source/SPArrayAdditions.h new file mode 100644 index 00000000..d1084ad7 --- /dev/null +++ b/Source/SPArrayAdditions.h @@ -0,0 +1,29 @@ +// +// SPArrayAdditions.h +// sequel-pro +// +// Created by Jakob Egger on March 24, 2009 +// +// This program is free software; you can redistribute it and/or modify +// it under the terms of the GNU General Public License as published by +// the Free Software Foundation; either version 2 of the License, or +// (at your option) any later version. +// +// This program is distributed in the hope that it will be useful, +// but WITHOUT ANY WARRANTY; without even the implied warranty of +// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +// GNU General Public License for more details. +// +// You should have received a copy of the GNU General Public License +// along with this program; if not, write to the Free Software +// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA +// +// More info at + +#import + +@interface NSArray (SPArrayAdditions) + +- (NSString *)componentsJoinedAndBacktickQuoted; + +@end diff --git a/Source/SPArrayAdditions.m b/Source/SPArrayAdditions.m new file mode 100644 index 00000000..c764b03b --- /dev/null +++ b/Source/SPArrayAdditions.m @@ -0,0 +1,43 @@ +// +// SPArrayAdditions.m +// sequel-pro +// +// Created by Jakob Egger on March 24, 2009 +// +// This program is free software; you can redistribute it and/or modify +// it under the terms of the GNU General Public License as published by +// the Free Software Foundation; either version 2 of the License, or +// (at your option) any later version. +// +// This program is distributed in the hope that it will be useful, +// but WITHOUT ANY WARRANTY; without even the implied warranty of +// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +// GNU General Public License for more details. +// +// You should have received a copy of the GNU General Public License +// along with this program; if not, write to the Free Software +// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA +// +// More info at + +#import "SPArrayAdditions.h" +#import "SPStringAdditions.h" + +@implementation NSArray (SPArrayAdditions) + +- (NSString *)componentsJoinedAndBacktickQuoted; +/* + * This method quotes all elements with backticks and then joins them with + * commas. Use it for field lists as in "SELECT (...) FROM somewhere" + */ +{ + NSString *result = [NSString string]; + for (NSString *component in self) + { + if ([result length]) result = [result stringByAppendingString: @","]; + result = [result stringByAppendingString: [component backtickQuotedString] ]; + } + return result; +} + +@end diff --git a/Source/SPStringAdditions.h b/Source/SPStringAdditions.h index 0a323cf3..be999ba4 100644 --- a/Source/SPStringAdditions.h +++ b/Source/SPStringAdditions.h @@ -27,6 +27,8 @@ + (NSString *)stringForByteSize:(int)byteSize; + (NSString *)stringForTimeInterval:(float)timeInterval; +- (NSString *)backtickQuotedString; + #if MAC_OS_X_VERSION_MAX_ALLOWED < MAC_OS_X_VERSION_10_5 - (NSArray *)componentsSeparatedByCharactersInSet:(NSCharacterSet *)set; #endif diff --git a/Source/SPStringAdditions.m b/Source/SPStringAdditions.m index ad6972ce..ef595dcc 100644 --- a/Source/SPStringAdditions.m +++ b/Source/SPStringAdditions.m @@ -111,6 +111,18 @@ } +// ------------------------------------------------------------------------------- +// backtickQuotedString +// +// Returns the string quoted with backticks as required for MySQL identifiers +// eg.: tablename => `tablename` +// my`table => `my``table` +// ------------------------------------------------------------------------------- +- (NSString *)backtickQuotedString +{ + return [NSString stringWithFormat: @"`%@`", [self stringByReplacingOccurrencesOfString: @"`" withString: @"``"] ]; +} + #if MAC_OS_X_VERSION_MAX_ALLOWED < MAC_OS_X_VERSION_10_5 // ------------------------------------------------------------------------------- // componentsSeparatedByCharactersInSet: diff --git a/Source/SPTableData.m b/Source/SPTableData.m index 7561b1ce..1b4893ae 100644 --- a/Source/SPTableData.m +++ b/Source/SPTableData.m @@ -28,6 +28,7 @@ #import "SPSQLParser.h" #import "TableDocument.h" #import "TablesList.h" +#import "SPStringAdditions.h" @implementation SPTableData @@ -259,7 +260,9 @@ if ([tableName isEqualToString:@""] || !tableName) return nil; // Retrieve the CREATE TABLE syntax for the table - CMMCPResult *theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE `%@`", tableName]]; + CMMCPResult *theResult = [mySQLConnection queryString: [NSString stringWithFormat: @"SHOW CREATE TABLE %@", + [tableName backtickQuotedString] + ]]; // Check for any errors, but only display them if a connection still exists if (![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) { @@ -302,9 +305,25 @@ // If the first character is a backtick, this is a field definition. if ([fieldsParser characterAtIndex:0] =='`') { - - // Capture the area between the two backticks as the name - [tableColumn setObject:[fieldsParser trimAndReturnStringFromCharacter:'`' toCharacter:'`' trimmingInclusively:YES returningInclusively:NO ignoringQuotedStrings:NO] forKey:@"name"]; + + // Capture the area between the two backticks as the name + NSString *fieldName = [fieldsParser trimAndReturnStringFromCharacter: '`' + toCharacter: '`' + trimmingInclusively: YES + returningInclusively: NO + ignoringQuotedStrings: NO]; + //if the next character is again a backtick, we stumbled across an escaped backtick. we have to continue parsing. + while ([fieldsParser characterAtIndex:0] =='`') { + fieldName = [fieldName stringByAppendingFormat: @"`%@", + [fieldsParser trimAndReturnStringFromCharacter: '`' + toCharacter: '`' + trimmingInclusively: YES + returningInclusively: NO + ignoringQuotedStrings: NO] + ]; + } + + [tableColumn setObject:fieldName forKey:@"name"]; // Split the remaining field definition string by spaces and process [tableColumn addEntriesFromDictionary:[self parseFieldDefinitionStringParts:[fieldsParser splitStringByCharacter:' ' skippingBrackets:YES]]]; @@ -416,7 +435,7 @@ if ([viewName isEqualToString:@""] || !viewName) return nil; // Retrieve the SHOW COLUMNS syntax for the table - CMMCPResult *theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM `%@`", viewName]]; + CMMCPResult *theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@", [viewName backtickQuotedString]]]; // Check for any errors, but only display them if a connection still exists if (![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) { diff --git a/Source/TableContent.m b/Source/TableContent.m index 69c1641a..67bb68b9 100644 --- a/Source/TableContent.m +++ b/Source/TableContent.m @@ -30,6 +30,8 @@ #import "SPDataCellFormatter.h" #import "SPTableData.h" #import "SPQueryConsole.h" +#import "SPStringAdditions.h" +#import "SPArrayAdditions.h" @implementation TableContent @@ -268,9 +270,9 @@ [removeButton setEnabled:YES]; // Perform the data query and store the result as an array containing a dictionary per result row - query = [NSString stringWithFormat:@"SELECT %@ FROM `%@`", [self fieldListForQuery], selectedTable]; + query = [NSString stringWithFormat:@"SELECT %@ FROM %@", [self fieldListForQuery], [selectedTable backtickQuotedString]]; if ( sortField ) { - query = [NSString stringWithFormat:@"%@ ORDER BY `%@`", query, sortField]; + query = [NSString stringWithFormat:@"%@ ORDER BY %@", query, [sortField backtickQuotedString]]; if ( isDesc ) query = [query stringByAppendingString:@" DESC"]; } @@ -357,10 +359,10 @@ } // queryString = [@"SELECT * FROM " stringByAppendingString:selectedTable]; - queryString = [NSString stringWithFormat:@"SELECT %@ FROM `%@`", [self fieldListForQuery], selectedTable]; + queryString = [NSString stringWithFormat:@"SELECT %@ FROM %@", [self fieldListForQuery], [selectedTable backtickQuotedString]]; if ( sortField ) { - queryString = [NSString stringWithFormat:@"%@ ORDER BY `%@`", queryString, sortField]; - // queryString = [queryString stringByAppendingString:[NSString stringWithFormat:@" ORDER BY `%@`", sortField]]; + queryString = [NSString stringWithFormat:@"%@ ORDER BY %@", queryString, [sortField backtickQuotedString]]; + // queryString = [queryString stringByAppendingString:[NSString stringWithFormat:@" ORDER BY %@", [sortField backtickQuotedString]]]; if ( isDesc ) queryString = [queryString stringByAppendingString:@" DESC"]; } @@ -429,7 +431,7 @@ BOOL ignoreArgument = NO; // Start building the query string - queryString = [NSString stringWithFormat:@"SELECT %@ FROM `%@`", [self fieldListForQuery], selectedTable]; + queryString = [NSString stringWithFormat:@"SELECT %@ FROM %@", [self fieldListForQuery], [selectedTable backtickQuotedString]]; // Add filter if appropriate if (([argument length] > 0) || [[[compareField selectedItem] title] hasSuffix:@"NULL"]) { @@ -551,11 +553,11 @@ } } [argument setString:[mySQLConnection prepareString:argument]]; - queryString = [NSString stringWithFormat:@"%@ WHERE `%@` %@ \"%@\"", - queryString, [fieldField titleOfSelectedItem], compareOperator, argument]; + queryString = [NSString stringWithFormat:@"%@ WHERE %@ %@ \"%@\"", + queryString, [[fieldField titleOfSelectedItem] backtickQuotedString], compareOperator, argument]; } else { - queryString = [NSString stringWithFormat:@"%@ WHERE `%@` %@ %@", - queryString, [fieldField titleOfSelectedItem], + queryString = [NSString stringWithFormat:@"%@ WHERE %@ %@ %@", + queryString, [[fieldField titleOfSelectedItem] backtickQuotedString], compareOperator, (ignoreArgument) ? @"" : argument]; } } @@ -563,7 +565,7 @@ // Add sorting details if appropriate if ( sortField ) { - queryString = [NSString stringWithFormat:@"%@ ORDER BY `%@`", queryString, sortField]; + queryString = [NSString stringWithFormat:@"%@ ORDER BY %@", queryString, [sortField backtickQuotedString]]; if ( isDesc ) queryString = [queryString stringByAppendingString:@" DESC"]; } @@ -681,13 +683,13 @@ return; } //if we have indexes, use argumentForRow - queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM `%@` WHERE %@", selectedTable, [self argumentForRow:[tableContentView selectedRow]]]]; + queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@", [selectedTable backtickQuotedString], [self argumentForRow:[tableContentView selectedRow]]]]; dbDataRow = [queryResult fetchRowAsDictionary]; } //set autoincrement fields to NULL - queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM `%@`", selectedTable]]; + queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@", [selectedTable backtickQuotedString]]]; if ([queryResult numOfRows]) [queryResult dataSeek:0]; for ( i = 0 ; i < [queryResult numOfRows] ; i++ ) { row = [queryResult fetchRowAsDictionary]; @@ -1229,18 +1231,18 @@ // Use INSERT syntax when creating new rows if ( isEditingNewRow ) { - queryString = [NSString stringWithFormat:@"INSERT INTO `%@` (`%@`) VALUES (%@)", - selectedTable, [columnNames componentsJoinedByString:@"`,`"], [fieldValues componentsJoinedByString:@","]]; + queryString = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@)", + [selectedTable backtickQuotedString], [columnNames componentsJoinedAndBacktickQuoted], [fieldValues componentsJoinedByString:@","]]; // Use UPDATE syntax otherwise } else { - queryString = [NSMutableString stringWithFormat:@"UPDATE `%@` SET ", selectedTable]; + queryString = [NSMutableString stringWithFormat:@"UPDATE %@ SET ", [selectedTable backtickQuotedString]]; for ( i = 0 ; i < [columnNames count] ; i++ ) { if ( i > 0 ) { [queryString appendString:@", "]; } - [queryString appendString:[NSString stringWithFormat:@"`%@`=%@", - [columnNames objectAtIndex:i], [fieldValues objectAtIndex:i]]]; + [queryString appendString:[NSString stringWithFormat:@"%@=%@", + [[columnNames objectAtIndex:i] backtickQuotedString], [fieldValues objectAtIndex:i]]]; } [queryString appendString:[NSString stringWithFormat:@" WHERE %@", [self argumentForRow:-2]]]; } @@ -1294,9 +1296,9 @@ // TODO: this probably needs looking at... it's reloading it all itself? } else { - query = [NSString stringWithFormat:@"SELECT %@ FROM `%@`", [self fieldListForQuery], selectedTable]; + query = [NSString stringWithFormat:@"SELECT %@ FROM %@", [self fieldListForQuery], [selectedTable backtickQuotedString]]; if ( sortField ) { - query = [NSString stringWithFormat:@"%@ ORDER BY `%@`", query, sortField]; + query = [NSString stringWithFormat:@"%@ ORDER BY %@", query, [sortField backtickQuotedString]]; if ( isDesc ) query = [query stringByAppendingString:@" DESC"]; } @@ -1379,7 +1381,7 @@ if ( !keys ) { setLimit = NO; keys = [[NSMutableArray alloc] init]; - theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM `%@`", selectedTable]]; + theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@", [selectedTable backtickQuotedString]]]; if ([theResult numOfRows]) [theResult dataSeek:0]; for ( i = 0 ; i < [theResult numOfRows] ; i++ ) { theRow = [theResult fetchRowAsDictionary]; @@ -1430,7 +1432,7 @@ } if ( [value isEqualToString:[prefs stringForKey:@"nullValue"]] ) { - [argument appendString:[NSString stringWithFormat:@"`%@` IS NULL", [keys objectAtIndex:i]]]; + [argument appendString:[NSString stringWithFormat:@"%@ IS NULL", [[keys objectAtIndex:i] backtickQuotedString]]]; } else { // Escape special characters (in WHERE statement!) @@ -1452,9 +1454,9 @@ columnType = [[tableDataInstance columnWithName:[keys objectAtIndex:i]] objectForKey:@"typegrouping"]; if ( [columnType isEqualToString:@"integer"] || [columnType isEqualToString:@"float"] || [columnType isEqualToString:@"bit"] ) { - [argument appendString:[NSString stringWithFormat:@"`%@` = %@", [keys objectAtIndex:i], value]]; + [argument appendString:[NSString stringWithFormat:@"%@ = %@", [[keys objectAtIndex:i] backtickQuotedString], value]]; } else { - [argument appendString:[NSString stringWithFormat:@"`%@` LIKE %@", [keys objectAtIndex:i], value]]; + [argument appendString:[NSString stringWithFormat:@"%@ LIKE %@", [[keys objectAtIndex:i] backtickQuotedString], value]]; } } } @@ -1502,9 +1504,9 @@ // Always select at least one field - the first if there are no non-blob fields. if ( [fields count] == 0 ) { - return [NSString stringWithFormat:@"`%@`", [columnNames objectAtIndex:0]]; + return [[columnNames objectAtIndex:0] backtickQuotedString]; } else { - return [NSString stringWithFormat:@"`%@`", [fields componentsJoinedByString:@"`,`"]]; + return [fields componentsJoinedAndBacktickQuoted]; } } else { return @"*"; @@ -1551,7 +1553,7 @@ areShowingAllRows && ([tableContentView numberOfSelectedRows] < [prefs integerForKey:@"limitRowsValue"]) ) { */ - [mySQLConnection queryString:[NSString stringWithFormat:@"DELETE FROM `%@`", selectedTable]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"DELETE FROM %@", [selectedTable backtickQuotedString]]]; if ( [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { [self reloadTable:self]; } else { @@ -1568,7 +1570,7 @@ wherePart = [NSString stringWithString:[self argumentForRow:[index intValue]]]; //argumentForRow might return empty query, in which case we shouldn't execute the partial query if([wherePart length] > 0) { - [mySQLConnection queryString:[NSString stringWithFormat:@"DELETE FROM `%@` WHERE %@", selectedTable, wherePart]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"DELETE FROM %@ WHERE %@", [selectedTable backtickQuotedString], wherePart]]; if ( ![mySQLConnection affectedRows] ) { //no rows deleted errors++; @@ -1601,10 +1603,10 @@ numRows = [self getNumberOfRows]; if ( !areShowingAllRows ) { // queryString = [@"SELECT * FROM " stringByAppendingString:selectedTable]; - queryString = [NSString stringWithFormat:@"SELECT %@ FROM `%@`", [self fieldListForQuery], selectedTable]; + queryString = [NSString stringWithFormat:@"SELECT %@ FROM %@", [self fieldListForQuery], [selectedTable backtickQuotedString]]; if ( sortField ) { - // queryString = [queryString stringByAppendingString:[NSString stringWithFormat:@" ORDER BY `%@`", sortField]]; - queryString = [NSString stringWithFormat:@"%@ ORDER BY `%@`", queryString, sortField]; + // queryString = [queryString stringByAppendingString:[NSString stringWithFormat:@" ORDER BY %@", [sortField backtickQuotedString]]]; + queryString = [NSString stringWithFormat:@"%@ ORDER BY %@", queryString, [sortField backtickQuotedString]]; if ( isDesc ) queryString = [queryString stringByAppendingString:@" DESC"]; } @@ -1654,7 +1656,7 @@ */ - (int)fetchNumberOfRows { - return [[[[mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(1) FROM `%@`", selectedTable]] fetchRowAsArray] objectAtIndex:0] intValue]; + return [[[[mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(1) FROM %@", [selectedTable backtickQuotedString]]] fetchRowAsArray] objectAtIndex:0] intValue]; } //tableView datasource methods @@ -1740,8 +1742,8 @@ objectValueForTableColumn:(NSTableColumn *)aTableColumn sortField = [tableColumn identifier]; //make queryString and perform query - queryString = [NSString stringWithFormat:@"SELECT %@ FROM `%@` ORDER BY `%@`", [self fieldListForQuery], - selectedTable, sortField]; + queryString = [NSString stringWithFormat:@"SELECT %@ FROM %@ ORDER BY %@", [self fieldListForQuery], + [selectedTable backtickQuotedString], [sortField backtickQuotedString]]; if ( isDesc ) queryString = [queryString stringByAppendingString:@" DESC"]; if ( [prefs boolForKey:@"limitRows"] ) { @@ -1879,7 +1881,7 @@ objectValueForTableColumn:(NSTableColumn *)aTableColumn wherePart = [NSString stringWithString:[self argumentForRow:[tableContentView selectedRow]]]; if([wherePart length]==0) return NO; - query = [NSString stringWithFormat:@"SELECT * FROM `%@` WHERE %@", selectedTable, wherePart]; + query = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@", [selectedTable backtickQuotedString], wherePart]; tempResult = [mySQLConnection queryString:query]; if ( ![tempResult numOfRows] ) { NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil, diff --git a/Source/TableDocument.m b/Source/TableDocument.m index 8596f60b..c4179fb4 100644 --- a/Source/TableDocument.m +++ b/Source/TableDocument.m @@ -36,6 +36,7 @@ #import "SPQueryConsole.h" #import "SPSQLParser.h" #import "SPTableData.h" +#import "SPStringAdditions.h" NSString *TableDocumentFavoritesControllerSelectionIndexDidChange = @"TableDocumentFavoritesControllerSelectionIndexDidChange"; NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFavoritesControllerFavoritesDidChange"; @@ -412,7 +413,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa if (returnCode != NSAlertDefaultReturn) return; - [mySQLConnection queryString:[NSString stringWithFormat:@"DROP DATABASE `%@`", [self database]]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"DROP DATABASE %@", [[self database] backtickQuotedString]]]; if (![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) { // error while deleting db NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, nil, nil, nil, [NSString stringWithFormat:NSLocalizedString(@"Couldn't remove database.\nMySQL said: %@", @"message of panel when removing db failed"), [mySQLConnection getLastErrorMessage]]); @@ -535,11 +536,11 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa return; } - NSString *createStatement = [NSString stringWithFormat:@"CREATE DATABASE `%@`", [databaseNameField stringValue]]; + NSString *createStatement = [NSString stringWithFormat:@"CREATE DATABASE %@", [[databaseNameField stringValue] backtickQuotedString]]; // If there is an encoding selected other than the default we must specify it in CREATE DATABASE statement if ([databaseEncodingButton indexOfSelectedItem] > 0) { - createStatement = [NSString stringWithFormat:@"%@ DEFAULT CHARACTER SET `%@`", createStatement, [self mysqlEncodingFromDisplayEncoding:[databaseEncodingButton title]]]; + createStatement = [NSString stringWithFormat:@"%@ DEFAULT CHARACTER SET %@", createStatement, [[self mysqlEncodingFromDisplayEncoding:[databaseEncodingButton title]] backtickQuotedString]]; } // Create the database @@ -787,7 +788,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa - (IBAction)showCreateTableSyntax:(id)sender { //Create the query and get results - NSString *query = [NSString stringWithFormat:@"SHOW CREATE TABLE `%@`", [self table]]; + NSString *query = [NSString stringWithFormat:@"SHOW CREATE TABLE %@", [[self table] backtickQuotedString]]; CMMCPResult *theResult = [mySQLConnection queryString:query]; // Check for errors @@ -808,7 +809,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa - (IBAction)copyCreateTableSyntax:(id)sender { // Create the query and get results - NSString *query = [NSString stringWithFormat:@"SHOW CREATE TABLE `%@`", [self table]]; + NSString *query = [NSString stringWithFormat:@"SHOW CREATE TABLE %@", [[self table] backtickQuotedString]]; CMMCPResult *theResult = [mySQLConnection queryString:query]; // Check for errors @@ -840,7 +841,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa NSDictionary *theRow; //Create the query and get results - query = [NSString stringWithFormat:@"CHECK TABLE `%@`", [self table]]; + query = [NSString stringWithFormat:@"CHECK TABLE %@", [[self table] backtickQuotedString]]; theResult = [mySQLConnection queryString:query]; // Check for errors @@ -861,7 +862,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa NSDictionary *theRow; //Create the query and get results - query = [NSString stringWithFormat:@"ANALYZE TABLE `%@`", [self table]]; + query = [NSString stringWithFormat:@"ANALYZE TABLE %@", [[self table] backtickQuotedString]]; theResult = [mySQLConnection queryString:query]; // Check for errors @@ -882,7 +883,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa NSDictionary *theRow; //Create the query and get results - query = [NSString stringWithFormat:@"OPTIMIZE TABLE `%@`", [self table]]; + query = [NSString stringWithFormat:@"OPTIMIZE TABLE %@", [[self table] backtickQuotedString]]; theResult = [mySQLConnection queryString:query]; // Check for errors @@ -902,7 +903,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa NSDictionary *theRow; //Create the query and get results - query = [NSString stringWithFormat:@"REPAIR TABLE `%@`", [self table]]; + query = [NSString stringWithFormat:@"REPAIR TABLE %@", [[self table] backtickQuotedString]]; theResult = [mySQLConnection queryString:query]; // Check for errors @@ -921,7 +922,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa CMMCPResult *theResult; //Create the query and get results - query = [NSString stringWithFormat:@"FLUSH TABLE `%@`", [self table]]; + query = [NSString stringWithFormat:@"FLUSH TABLE %@", [[self table] backtickQuotedString]]; theResult = [mySQLConnection queryString:query]; // Check for errors @@ -941,7 +942,7 @@ NSString *TableDocumentFavoritesControllerFavoritesDidChange = @"TableDocumentFa NSDictionary *theRow; //Create the query and get results - query = [NSString stringWithFormat:@"CHECKSUM TABLE `%@`", [self table]]; + query = [NSString stringWithFormat:@"CHECKSUM TABLE %@", [[self table] backtickQuotedString]]; theResult = [mySQLConnection queryString:query]; // Check for errors diff --git a/Source/TableDump.m b/Source/TableDump.m index a59aad73..0f845921 100644 --- a/Source/TableDump.m +++ b/Source/TableDump.m @@ -31,6 +31,8 @@ #import "SPGrowlController.h" #import "SPSQLParser.h" #import "SPTableData.h" +#import "SPStringAdditions.h" +#import "SPArrayAdditions.h" @implementation TableDump @@ -589,7 +591,7 @@ if ( [fNames length] ) [fNames appendString:@","]; - [fNames appendString:[NSString stringWithFormat:@"`%@`", [[tableSourceInstance fieldNames] objectAtIndex:i]]]; + [fNames appendString:[[[tableSourceInstance fieldNames] objectAtIndex:i] backtickQuotedString]]; } } @@ -618,8 +620,8 @@ } //perform query - [mySQLConnection queryString:[NSString stringWithFormat:@"INSERT INTO `%@` (%@) VALUES (%@)", - [fieldMappingPopup titleOfSelectedItem], + [mySQLConnection queryString:[NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@)", + [[fieldMappingPopup titleOfSelectedItem] backtickQuotedString], fNames, fValues]]; @@ -818,12 +820,12 @@ // Add a "drop table" command if specified in the export dialog if ( [addDropTableSwitch state] == NSOnState ) - [fileHandle writeData:[[NSString stringWithFormat:@"DROP TABLE IF EXISTS `%@`;\n\n", tableName] + [fileHandle writeData:[[NSString stringWithFormat:@"DROP TABLE IF EXISTS %@;\n\n", [tableName backtickQuotedString]] dataUsingEncoding:connectionEncoding]]; // Add the create syntax for the table if specified in the export dialog if ( [addCreateTableSwitch state] == NSOnState ) { - queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE `%@`", tableName]]; + queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE %@", [tableName backtickQuotedString]]]; if ( [queryResult numOfRows] ) { createTableSyntax = [[queryResult fetchRowAsDictionary] objectForKey:@"Create Table"]; if ( [createTableSyntax isKindOfClass:[NSData class]] ) { @@ -842,7 +844,7 @@ // Add the table content if required if ( [addTableContentSwitch state] == NSOnState ) { - queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM `%@`", tableName]]; + queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM %@", [tableName backtickQuotedString]]]; fieldNames = [queryResult fetchFieldNames]; rowCount = [queryResult numOfRows]; @@ -874,8 +876,8 @@ queryLength = 0; // Construct the start of the insertion command - [fileHandle writeData:[[NSString stringWithFormat:@"INSERT INTO `%@` (`%@`)\nVALUES\n\t(", - tableName, [fieldNames componentsJoinedByString:@"`,`"]] dataUsingEncoding:connectionEncoding]]; + [fileHandle writeData:[[NSString stringWithFormat:@"INSERT INTO %@ (%@)\nVALUES\n\t(", + [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]] dataUsingEncoding:connectionEncoding]]; // Iterate through the rows to construct a VALUES group for each for ( j = 0 ; j < rowCount ; j++ ) { @@ -934,8 +936,8 @@ // Add a new INSERT starter command every ~250k of data. if (queryLength > 250000) { - [sqlString appendString:[NSString stringWithFormat:@");\n\nINSERT INTO `%@` (`%@`)\nVALUES\n\t(", - tableName, [fieldNames componentsJoinedByString:@"`,`"]]]; + [sqlString appendString:[NSString stringWithFormat:@");\n\nINSERT INTO %@ (%@)\nVALUES\n\t(", + [tableName backtickQuotedString], [fieldNames componentsJoinedAndBacktickQuoted]]]; queryLength = 0; } else { [sqlString appendString:@"),\n\t("]; @@ -1591,7 +1593,7 @@ } // Retrieve all the content within this table - queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM `%@`", tableName]]; + queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT * FROM %@", [tableName backtickQuotedString]]]; // Note any errors during retrieval if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { diff --git a/Source/TableSource.m b/Source/TableSource.m index 5d1c8e43..b9fd59e6 100644 --- a/Source/TableSource.m +++ b/Source/TableSource.m @@ -25,6 +25,8 @@ #import "TableSource.h" #import "TablesList.h" #import "SPTableData.h" +#import "SPStringAdditions.h" +#import "SPArrayAdditions.h" @implementation TableSource @@ -80,7 +82,7 @@ loads aTable, put it in an array, update the tableViewColumns and reload the tab [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:self]; //perform queries and load results in array (each row as a dictionary) - tableSourceResult = [[mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM `%@`", selectedTable]] retain]; + tableSourceResult = [[mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@", [selectedTable backtickQuotedString]]] retain]; // listFieldsFromTable is broken in the current version of the framework (no back-ticks for table name)! // tableSourceResult = [[mySQLConnection listFieldsFromTable:selectedTable] retain]; @@ -88,7 +90,7 @@ loads aTable, put it in an array, update the tableViewColumns and reload the tab [tableFields setArray:[self fetchResultAsArray:tableSourceResult]]; [tableSourceResult release]; - indexResult = [[mySQLConnection queryString:[NSString stringWithFormat:@"SHOW INDEX FROM `%@`", selectedTable]] retain]; + indexResult = [[mySQLConnection queryString:[NSString stringWithFormat:@"SHOW INDEX FROM %@", [selectedTable backtickQuotedString]]] retain]; // [indexes setArray:[[self fetchResultAsArray:indexResult] retain]]; [indexes setArray:[self fetchResultAsArray:indexResult]]; [indexResult release]; @@ -293,7 +295,7 @@ adds the index to the mysql-db and stops modal session with code 1 when success, { indexName = @""; } else { - indexName = [NSString stringWithFormat:@"`%@`", [indexNameField stringValue]]; + indexName = [[indexNameField stringValue] backtickQuotedString]; } } indexedColumns = [[indexedColumnsField stringValue] componentsSeparatedByString:@","]; @@ -306,14 +308,14 @@ adds the index to the mysql-db and stops modal session with code 1 when success, } } - [mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE `%@` ADD %@ %@ (`%@`)", - selectedTable, [indexTypeField titleOfSelectedItem], indexName, - [tempIndexedColumns componentsJoinedByString:@"`,`"]]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@ (%@)", + [selectedTable backtickQuotedString], [indexTypeField titleOfSelectedItem], indexName, + [tempIndexedColumns componentsJoinedAndBacktickQuoted]]]; /* -NSLog([NSString stringWithFormat:@"ALTER TABLE `%@` ADD %@ %@ (`%@`)", - selectedTable, [indexTypeField titleOfSelectedItem], indexName, - [[[indexedColumnsField stringValue] componentsSeparatedByString:@","] componentsJoinedByString:@"`,`"]]); +NSLog([NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@ (%@)", + [selectedTable backtickQuotedString], [indexTypeField titleOfSelectedItem], indexName, + [tempIndexedColumns componentsJoinedAndBacktickQuoted]]); */ if ( [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { @@ -372,7 +374,7 @@ opens alertsheet and asks for confirmation // alert any listeners that we are about to perform a query. [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:self]; - NSString *query = [NSString stringWithFormat:@"ALTER TABLE `%@` TYPE = %@",selectedTable,selectedItem]; + NSString *query = [NSString stringWithFormat:@"ALTER TABLE %@ TYPE = %@",[selectedTable backtickQuotedString],selectedItem]; [mySQLConnection queryString:query]; // The query is now complete. @@ -606,22 +608,22 @@ returns YES if no row is beeing edited and nothing has to be written to db if ( isEditingNewRow ) { //ADD syntax if ( [[theRow objectForKey:@"Length"] isEqualToString:@""] || ![theRow objectForKey:@"Length"] ) { - queryString = [NSMutableString stringWithFormat:@"ALTER TABLE `%@` ADD `%@` %@", - selectedTable, [theRow objectForKey:@"Field"], [theRow objectForKey:@"Type"]]; + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@", + [selectedTable backtickQuotedString], [[theRow objectForKey:@"Field"] backtickQuotedString], [theRow objectForKey:@"Type"]]; } else { - queryString = [NSMutableString stringWithFormat:@"ALTER TABLE `%@` ADD `%@` %@(%@)", - selectedTable, [theRow objectForKey:@"Field"], [theRow objectForKey:@"Type"], + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@(%@)", + [selectedTable backtickQuotedString], [[theRow objectForKey:@"Field"] backtickQuotedString], [theRow objectForKey:@"Type"], [theRow objectForKey:@"Length"]]; } } else { //CHANGE syntax if (([[theRow objectForKey:@"Length"] isEqualToString:@""]) || (![theRow objectForKey:@"Length"]) || ([[theRow objectForKey:@"Type"] isEqualToString:@"datetime"])) { - queryString = [NSMutableString stringWithFormat:@"ALTER TABLE `%@` CHANGE `%@` `%@` %@", - selectedTable, [oldRow objectForKey:@"Field"], [theRow objectForKey:@"Field"], + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ CHANGE %@ %@ %@", + [selectedTable backtickQuotedString], [[oldRow objectForKey:@"Field"] backtickQuotedString], [[theRow objectForKey:@"Field"] backtickQuotedString], [theRow objectForKey:@"Type"]]; } else { - queryString = [NSMutableString stringWithFormat:@"ALTER TABLE `%@` CHANGE `%@` `%@` %@(%@)", - selectedTable, [oldRow objectForKey:@"Field"], [theRow objectForKey:@"Field"], + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ CHANGE %@ %@ %@(%@)", + [selectedTable backtickQuotedString], [[oldRow objectForKey:@"Field"] backtickQuotedString], [[theRow objectForKey:@"Field"] backtickQuotedString], [theRow objectForKey:@"Type"], [theRow objectForKey:@"Length"]]; } } @@ -675,8 +677,8 @@ returns YES if no row is beeing edited and nothing has to be written to db if ( [chooseKeyButton indexOfSelectedItem] == 0 ) { [queryString appendString:@" PRIMARY KEY"]; } else { - [queryString appendString:[NSString stringWithFormat:@", ADD %@ (`%@`)", - [chooseKeyButton titleOfSelectedItem], [theRow objectForKey:@"Field"]]]; + [queryString appendString:[NSString stringWithFormat:@", ADD %@ (%@)", + [chooseKeyButton titleOfSelectedItem], [[theRow objectForKey:@"Field"] backtickQuotedString]]]; } } } @@ -743,8 +745,8 @@ returns YES if no row is beeing edited and nothing has to be written to db } else if ( [contextInfo isEqualToString:@"removefield"] ) { if ( returnCode == NSAlertDefaultReturn ) { //remove row - [mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE `%@` DROP `%@`", - selectedTable, [[tableFields objectAtIndex:[tableSourceView selectedRow]] objectForKey:@"Field"]]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE %@ DROP %@", + [selectedTable backtickQuotedString], [[[tableFields objectAtIndex:[tableSourceView selectedRow]] objectForKey:@"Field"] backtickQuotedString]]]; if ( [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { [self loadTable:selectedTable]; @@ -763,10 +765,10 @@ returns YES if no row is beeing edited and nothing has to be written to db if ( returnCode == NSAlertDefaultReturn ) { //remove index if ( [[[indexes objectAtIndex:[indexView selectedRow]] objectForKey:@"Key_name"] isEqualToString:@"PRIMARY"] ) { - [mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE `%@` DROP PRIMARY KEY", selectedTable]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE %@ DROP PRIMARY KEY", [selectedTable backtickQuotedString]]]; } else { - [mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE `%@` DROP INDEX `%@`", - selectedTable, [[indexes objectAtIndex:[indexView selectedRow]] objectForKey:@"Key_name"]]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"ALTER TABLE %@ DROP INDEX %@", + [selectedTable backtickQuotedString], [[[indexes objectAtIndex:[indexView selectedRow]] objectForKey:@"Key_name"] backtickQuotedString]]]; } if ( [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { @@ -938,8 +940,8 @@ would result in a position change. [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryWillBePerformed" object:self]; // Begin construction of the reordering query - queryString = [NSMutableString stringWithFormat:@"ALTER TABLE `%@` MODIFY COLUMN `%@` %@", selectedTable, - [originalRow objectForKey:@"Field"], + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ MODIFY COLUMN %@ %@", [selectedTable backtickQuotedString], + [[originalRow objectForKey:@"Field"] backtickQuotedString], [originalRow objectForKey:@"Type"]]; // Add the length parameter if necessary @@ -976,8 +978,8 @@ would result in a position change. if ( destinationRowIndex == 0 ){ [queryString appendString:@" FIRST"]; } else { - [queryString appendString:[NSString stringWithFormat:@" AFTER `%@`", - [[tableFields objectAtIndex:destinationRowIndex-1] objectForKey:@"Field"]]]; + [queryString appendString:[NSString stringWithFormat:@" AFTER %@", + [[[tableFields objectAtIndex:destinationRowIndex-1] objectForKey:@"Field"] backtickQuotedString]]]; } // Run the query; report any errors, or reload the table on success diff --git a/Source/TablesList.m b/Source/TablesList.m index 1f33d5ab..ef2b803c 100644 --- a/Source/TablesList.m +++ b/Source/TablesList.m @@ -31,6 +31,7 @@ #import "ImageAndTextCell.h" #import "CMMCPConnection.h" #import "CMMCPResult.h" +#import "SPStringAdditions.h" @implementation TablesList @@ -188,8 +189,9 @@ copies a table, if desired with content } //get table structure - queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE `%@`", - [tables objectAtIndex:[tablesListView selectedRow]]]]; + queryResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW CREATE TABLE %@", + [[tables objectAtIndex:[tablesListView selectedRow]] backtickQuotedString] + ]]; if ( ![queryResult numOfRows] ) { //error while getting table structure @@ -204,7 +206,7 @@ copies a table, if desired with content [scanner initWithString:[[queryResult fetchRowAsDictionary] objectForKey:@"Create Table"]]; [scanner scanUpToString:@"(" intoString:nil]; [scanner scanUpToString:@"" intoString:&scanString]; - [mySQLConnection queryString:[NSString stringWithFormat:@"CREATE TABLE `%@` %@", [copyTableNameField stringValue], scanString]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"CREATE TABLE %@ %@", [[copyTableNameField stringValue] backtickQuotedString], scanString]]; [scanner release]; if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { @@ -216,9 +218,9 @@ copies a table, if desired with content if ( [copyTableContentSwitch state] == NSOnState ) { //copy table content [mySQLConnection queryString:[NSString stringWithFormat: - @"INSERT INTO `%@` SELECT * FROM `%@`", - [copyTableNameField stringValue], - [tables objectAtIndex:[tablesListView selectedRow]] + @"INSERT INTO %@ SELECT * FROM %@", + [[copyTableNameField stringValue] backtickQuotedString], + [[tables objectAtIndex:[tablesListView selectedRow]] backtickQuotedString] ]]; if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { @@ -288,7 +290,9 @@ removes selected table(s) from mysql-db and tableView unsigned currentIndex = [indexes lastIndex]; while (currentIndex != NSNotFound) { - [mySQLConnection queryString:[NSString stringWithFormat:@"DROP TABLE `%@`", [tables objectAtIndex:currentIndex]]]; + [mySQLConnection queryString: [NSString stringWithFormat: @"DROP TABLE %@", + [[tables objectAtIndex:currentIndex] backtickQuotedString] + ]]; if ( [[mySQLConnection getLastErrorMessage] isEqualTo:@""] ) { //dropped table with success @@ -447,9 +451,9 @@ Mark the content table for refresh when it's next switched to [tablesListView reloadData]; } else { if ( [tableDocumentInstance supportsEncoding] ) { - [mySQLConnection queryString:[NSString stringWithFormat:@"CREATE TABLE `%@` (id int) DEFAULT CHARACTER SET %@", anObject, [tableDocumentInstance connectionEncoding]]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"CREATE TABLE %@ (id int) DEFAULT CHARACTER SET %@", [anObject backtickQuotedString], [tableDocumentInstance connectionEncoding]]]; } else { - [mySQLConnection queryString:[NSString stringWithFormat:@"CREATE TABLE `%@` (id int)", anObject]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"CREATE TABLE %@ (id int)", [anObject backtickQuotedString]]]; } if ( [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { @@ -506,7 +510,7 @@ Mark the content table for refresh when it's next switched to NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), nil, nil, tableWindow, self, @selector(sheetDidEnd:returnCode:contextInfo:), nil, @"addRow", NSLocalizedString(@"Table must have a name.", @"message of panel when no name is given for table")); } else { - [mySQLConnection queryString:[NSString stringWithFormat:@"RENAME TABLE `%@` TO `%@`", [tables objectAtIndex:rowIndex], anObject]]; + [mySQLConnection queryString:[NSString stringWithFormat:@"RENAME TABLE %@ TO %@", [[tables objectAtIndex:rowIndex] backtickQuotedString], [anObject backtickQuotedString]]]; if ( [[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) { // NSLog(@"renamed table with success"); //renamed with success -- cgit v1.2.3