1

MySQL 5.0.95 PHP 5.2.17

Hello,

I am using PHP to upload an XML file and import the XML data into MySQL tables. I am getting SQL syntax errors at the MySQL INSERT statement. The errors appear to be caused by single quotes, double quotes and/or special characters like bullet points. The problem data is $MarketCopy->MarketCopyContent which is being inserted into the 2nd column.

I have attempted combinations of the following functions with no success:

htmlspecialchars htmlentities addslashes mysqli::real_escape_string

I read that different character sets can cause issues like this so I have set the character set to UTF-8 as you can see in the code below.

Any advice on the proper way to avoid these errors is much appreciated.

Thanks,

Jay

mb_internal_encoding("UTF-8"); // Set internal character encoding

if (!$mysqli->set_charset('utf8')) { // Sets the default client character set
    printf("Error loading character set utf8: %s\n", $mysqli->error);
    exit;
}

$xmlstring = str_replace('xmlns=', 'ns=', file_get_contents($_FILES["file"]["tmp_name"])); // Creates string with contents of uploaded file and changes namespace to prevent xpath errors

$xml = new SimpleXMLElement($xmlstring); // Creates new SimpleXMLElement object

foreach ($xml->MarketingCopy->MarketCopy as $MarketCopy ) { // Iterates over XML array
    $sql = "
        INSERT INTO MarketCopyContent (
             MarketCopyReference
            ,MarketCopyContent
             )
            VALUES (
                 \"" . $MarketCopy->MarketCopyContent['MarketCopyReference'] . "\"
                ,\"" . $MarketCopy->MarketCopyContent . "\"
            );"; // MySQL statement
    echo $sql . "<br>"; // Show MySQL statement for testing
    perform_mysql_queries($mysqli, $sql); // User defined function that executes multiple MySQL statements
}
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
jgc9876
  • 85
  • 8
  • Can you please elaborate on how this question is the same as preventing SQL injection? Should I try prepared statements or is it about the encoding? – jgc9876 Oct 27 '15 at 16:06
  • This question was previously closed as duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174), which does not directly address the present question. Fred -ii- probably meant that the only secure way to solve your problem is by using prepared statements (then you don't even need to escape anymore). As you can see in the question I linked, attempting to escape a query is uncertain at best, and this approach is deprecated. – RandomSeed Oct 27 '15 at 16:32
  • Using prepared statements solved the issue. Just as you stated, the data imported without any escapes. Thank you for the response and clarification. – jgc9876 Oct 27 '15 at 20:35

0 Answers0