-2

I am trying to import a CSV file containing single quotation marks into the text. And each time, MySQL gives me an error. How could I avoid this type of error?

Here is one of the texts that causes problem:

Sam Capra, agent de la CIA basé à Londres, échappe à l'explosion de son bureau grâce à un appel de sa femme Lucy. Mais cette dernière, enceinte, disparaît et Sam est tenu responsable de l'attentat par la CIA.

and here is my SQL query:

 $sqlInsert = "INSERT into products_details (`product_title`,`product_price`, `product_image`, `product_gallery`,`product_description`, `meta_value`, `stock`, `weight`, `active`)
 values ('" . $column[2] . "','" . $column[21] . "','uploads/" . $column[0] . ".jpg','" . $column[1] . "','" . $column[22] . "','" . $json . "','" . (int)$column[15] . "','" . $column[20] . "','" . (int)$column[23] . "')";

Thank you in advance for your help!

Alon Eitan
  • 11,997
  • 8
  • 49
  • 58
user1987480
  • 83
  • 1
  • 1
  • 8
  • 4
    Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. Applying this technique will take care of all your quoting issues. – Alex Howansky Sep 21 '18 at 16:56
  • Where is you CVS file download code? You can write your own function to escape quotation mark in the string. – Klanto Aguntuk Sep 21 '18 at 17:06
  • And you already got some bad practice answers down here, so don't escape the string, don't replace comma with any other character and so on. Just use prepared statements (Follow the links in the first comment) – Alon Eitan Sep 21 '18 at 17:06
  • @AlexHowansky Yes I use a prepared before inserting via mysqli :) – user1987480 Sep 21 '18 at 18:49
  • Using a prepared statement is not sufficient. You need to use a prepared statement with bound parameters. – Alex Howansky Sep 21 '18 at 18:53
  • @AlexHowansky I decided to follow your advice and use PDO. And everything works perfectly. Thank you very much for the tips and links. – user1987480 Sep 21 '18 at 19:27

1 Answers1

0

notice tgat you have single quotes inside the text you should escape the string

I assume that $column[22] is the variable containing the text that you gave in the example, and let's say that $conn is your mysqli object Then this line shouls be added to your code, before you're insert string creation

$column[22] = mysqli_real_escape_string($conn, $column[22]) ;
Guy Louzon
  • 1,175
  • 9
  • 19