-1

I'm writing a python script to migrate data from one MySQL database to another. The script loads a sql file dumped by mysqldump and splits the commands similarly to this SO question. When I execute the script in python 2.7 using the python-mysql library, python outputs the following error: 

_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'\'\\"><script>alert(1)\' at line 1')

Checking the associated file, I see that python fails when it tries to execute a massive insert statement that includes 6 records like this: 

('58cd48g5158b8','2015-12-02 16:05:25','\"><script>alert(1);</script>','12.16.98.253')

But, if I were to execute the same file in MySQL Workbench, it completes without an error.

1. Why does MySQL server execute the file without problems, but mysql-python errors out?

2. Why does python treat the string as a a SQL syntax error?

Note: this table is the 13th to be processed in the script and the insert command is the 18th command for that table; so, I doubt it's an issue with my code.

Relevant Python Code

def loadMigrationScriptForTable(self, tableName, schemaName='obfuscated'):
    fileName = 'obfuscated' + schemaName + '.' + tableName + '.sql'
    fileDir = self.conf.get('obfuscated', 'obfuscated')
    workfile = os.path.join(fileDir, fileName)
    try:
        file = open(workfile, 'r')
        contents = file.read()
        return contents
    except IOError:
        return Exception('File does not exist: '+workfile)

# Migrate tables whose table definition has not been altered but row counts has changed.
def migrateTablesWithUnmodifiedDefinition(self):
    tables = self.conf.get('obfuscated', 'obfuscated').split(', ')
    responses = []
    for table in tables:
        # Load SQL file (as string)
        MySQLScript = self.loadMigrationScriptForTable(table)
        if(isinstance(MySQLScript, Exception)):
            print table, MySQLScript
            continue # Skip remaining code if file couldn't load.

        # Split commands
        sqlCommands = MySQLScript.split(';')
        for sqlCommand in sqlCommands:
            # Execute SQL file.
            print table, sqlCommand[0:99]
            response = self.db.executeScript(sqlCommand)
            print "        ", response
            responses.append(response)

def executeScript(self, script):
    self._DB.connection.execute(script)
KareemElashmawy
  • 251
  • 5
  • 16

1 Answers1

0

As Daniel Roseman hinted, it was an issue with my code. When parsing and splitting sql files generated by mysqldump you should split it based on the semicolon and newline character, otherwise string.split will split on semicolons inside strings.

Erroneous Code

# Split commands
sqlCommands = MySQLScript.split(';')

Fixed Code

# Split commands
sqlCommands = MySQLScript.split(';\n')

OS Note

Note: This works on windows; but, I have not tested it yet on *nix yet. If running on *nix, it may be more prudent to use string.splitlines. 

# Split commands
sqlCommands = MySQLScript.splitlines()
KareemElashmawy
  • 251
  • 5
  • 16