0

I have a a small application that gets the catalog from an api and with that catalog I wanna safe every product in to my own DB. everything works fine except the description. If I try to push that into my DB I get the error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's handkerchiefs. Gradually, she became a true symbol of Italian pizzazz between ' at line 1. I think its because sql doesn't accept punctuation marks but I'm not sure if that's the case

this is how I get the product ready to send it to my DB.php, everything goes fine until I send it to the DB

$xml = simplexml_load_string(file_get_contents($url));;
$xml->preserveWhiteSpace = false;
$xml->formatOutput = true;
$xml->saveXML();

$jsonList = json_decode(json_encode($xml));

//echo '<pre>'. print_r($xml->PRODUCT, true). '</pre>';
$lenght = count($xml);

$stack = array();
    foreach($jsonList->PRODUCT as $product){
    if($product->ID == 117758){
        //var_dump($product);
        array_push($stack, $product);
    }
}

foreach ($stack as $products) {
    SaveProduct($products);
}

And this is how I send it to the DB. (So this is my DB.php)

<?php
global $Connection;
function openConnection()
{

    global $Connection;
    $Connection = new mysqli("localhost", "root", "", "test");

    $Connection->select_db("test");

    if ($Connection->connect_error) {
        die("Connection failed: " . $Connection->connect_error);
    }
}

function SaveProduct($product){
    openConnection();
    global $Connection;

    if(is_string($product->DESCRIPTION)){
        $sql = "INSERT INTO product (`P_ID`,`Type`,`Manufacturer`,`Line`,`Name`,`Size`,`Variant`,`Variant_code`,`Variant_image`,`Sex`,`Image`,`Description`,`Price`,`Stock`,`Ean`,`Ean2`) VALUES ('".$product->ID."','".$product->TYPE."','".$product->MANUFACTURER."','".$product->LINE."','".$product->NAME."','".$product->SIZE."','NULL','NULL','NULL','".$product->SEX."','".$product->IMAGE."','".$product->DESCRIPTION."','".$product->PRICE."','".$product->STOCK."','".$product->EAN."','NULL')" or die("Error while adding product");
    }

    $result = $Connection->query($sql)or die($Connection->error);

    echo $result;
}

This is a picture of an $product and the error that I am getting

If u need more information let me know and i'll update this question! :)

Emin Ozdogan
  • 3
  • 2
  • 2
    You are wide open for SQL injection. Since you're using mysqli, take advantage of [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). **This will take care of any pesky quoting issues that may occur, such as quotes/apostrophes in text,** such as `men's` in your description – aynber Aug 26 '22 at 14:07
  • 1
    Regardless of the language: **never** build SQL strings containing values (e. g. given by user, files, db content etc.) using concatenation. – Mihe Aug 26 '22 at 14:11
  • @aynber I am using phpmyadmin and if i'm correct that isn't the same as mysqli, right? – Emin Ozdogan Aug 26 '22 at 14:13
  • Correct, they are completely not the same. mysqli is a library that allows your PHP script to connect to the database. PHPMyAdmin is a graphical interface to manage your database, as an alternative to the command line `mysql` command. – aynber Aug 26 '22 at 14:14
  • `if i'm correct that isn't the same as mysqli`...yes. PhpMyAdmin is not a database or a code library. It's a web based graphical administration tool for managing MySQL databases. (It is one of many such administration clients). Your PHP code connects directly to the MySQL database server and doesn't have anything to do with PhpMyAdmin. In this case, your PHP code is using the PHP "mysqli" code library to faciliate the connection to the mysql server. There are other such libraries (e.g. PDO) which also do the same thing. phpMyAdmin as a PHP app, probably uses one of them itself (mysqli I think) – ADyson Aug 26 '22 at 14:15
  • **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. – ADyson Aug 26 '22 at 14:16
  • https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of **un-escaped or incorrectly quoted input values**. If you learnt your current technique from a tutorial or book, please don't use it again. – ADyson Aug 26 '22 at 14:17
  • Also never configure your web app to login to the database as `root`. Root can do whatever it likes, so on top of the SQL injection vulnerabilities this just leaves your database an open book for hackers. Instead create a separate user account specifically for this application which has only the permissions it actually _needs_ in order to work properly. Don't even use the root account as a shortcut during development or testing, because you need to test your account permissions as well - otherwise when you go live you might have unexpected errors relating to the user account setup. – ADyson Aug 26 '22 at 14:17

0 Answers0