-3

My value has apostrophe like this example "Johnesia's book" and i want to insert it in the table but it interfere with sql query, And data is stored in the variable so i can't add backslash.

I've tried to add escapement on a single data and found to be perfect but data are from excel so i can't modify all data as they are so many here is my code `$msg = ''; if(isset($_POST['import'])){ $filename = $_FILES["file"]["tmp_name"];

if($_FILES["file"]["size"] > 0)
{        
    $file = fopen($filename, "r");
    while (($col = fgetcsv($file, 10000, ",")) !== FALSE) 
    {
         $insert = "INSERT INTO products (product_name,qty,price,s_price,profit)values('".$col[0]."','".$col[1]."','".$col[2]."','".$col[3]."','".$col[4]."')";
        mysqli_query($con,$insert);
    }
    $msg = '<p style="color: green;"> CSV Data inserted successfully</p>';
}

}`

Sample data is for item name is Neocast Plaster of Paris 10cm x 2.7m (1's)

this is the error message Fatal error: Uncaught mysqli_sql_exception: 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)','5','780','1500','720')' at line 1 in C:\xampp\htdocs\import-excel-to-mysql\index.php:24 Stack trace: #0 C:\xampp\htdocs\import-excel-to-mysql\index.php(24): mysqli_query(Object(mysqli), 'INSERT INTO pro...') #1 {main} thrown in C:\xampp\htdocs\import-excel-to-mysql\index.php on line 24

ADyson
  • 57,178
  • 14
  • 51
  • 63
Deus Leonard
  • 1
  • 2
  • 4
    [RTM](https://dev.mysql.com/doc/refman/8.0/en/string-literals.html) but you should be [using parameterised queries](https://phpdelusions.net/mysqli#prepare). As well as protecting against [SQL Injection](https://www.php.net/manual/en/security.database.sql-injection.php) and malformed queries, you will get a performance benefit too. – user1191247 Aug 31 '23 at 14:42
  • 3
    You're building your queries in completely the wrong way to begin with. Read [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) . This fixes not just the apostrophe problem, but other potential syntax issues and also, importantly, the massive SQL injection security hole you've created. I don't know where you were taught to build you queries the way you've done it here, but whoever showed you that way did you a massive disservice. – ADyson Aug 31 '23 at 14:44
  • There is no need to escape anything. Just use prepared statements. – Dharman Aug 31 '23 at 15:34

0 Answers0