How to use Str_Replace on Array Items in mySQL

Question

I know how to replace an Apostrophe from a String with a space, in php. However how can I remove Apostrophes in arrays items?

  //Example replacing Apostrophe with space
  $string = str_replace("'", "", $escapestring); 


 if ( $f_pointer === false )
    die ("invalid URL");

 $ar=fgetcsv($f_pointer);
 while(! feof($f_pointer))
 {
      $ar=fgetcsv($f_pointer);

      //DO REPLACE HERE BEFORE INSERTION INTO SQL STATEMENT
      $sql="INSERT INTO x
             (1,2,3,4,5,6) 
            values('$array[0]','$array[1]','$array[2]',
                   'array[3]','array[4]','array[5]')";

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Jul 30 '15 at 19:27
I understand that, I'm a beginner trying to gain a better understanding of mysql — Morry, Jul 30 '15 at 19:29
To gain a better under standing you should [not use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jul 30 '15 at 19:31
You should be using prepared statements but in the rare instance you have to manually escape something that is going to be part of a query use the database specific escaping function. — Orangepill, Jul 30 '15 at 19:39
`values('$array[0]','$array[1]','$array[2]', 'array[3]','array[4]','array[5]')"; ` you can do so `values('" . implode("','", $array) . "'";` — splash58, Jul 30 '15 at 20:18

schellingerht · Answer 1 · 2015-07-30T20:45:06.723

UPDATED answer:

You want to remove apostrophe for your query. It's a better way to use function as mysqli_real_escape_string:

"(PHP 5) mysqli::real_escape_string -- mysqli_real_escape_string — Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection" | http://php.net/manual/en/mysqli.real-escape-string.php

So you directly protect your query input against sql injection. See the following example:

$itemsToClean = ["Value with '", "Second value with '"];
// With older PHP version, you cannot use [], but use array() instead

$cleanItems = array_map('mysqli_real_escape_string', $itemsToClean);

print_r($cleanItems);

Could you add comments in your comment if thats ok, im struggling to understand how to deal with my $ar[0]','$ar[1]','$ar[2]','$ar[3]','$ar[4]','$ar[5 ] array values with your example — Morry, Jul 30 '15 at 19:59

score 1 · Answer 2 · answered Jul 30 '15 at 19:34

1

use str_replace with array as subject

$itemsToClean = ["Value with '", "Second value with '"];

$itemsToClean = str_replace("'", '', $itemsToClean);

print_r($itemsToClean);

result

Array
(
    [0] => Value with 
    [1] => Second value with 
)

answered Jul 30 '15 at 19:34

splash58

26,043
3
22
34

Thanks for that huge help, just wondering why is arg[5] in the results contain a Apostrophe? $ar = str_replace("'", '', $ar); print_r($ar); [0] => - [1] => Mary [2] => E [3] => 7:21 AM [4] => [5] => WOMENS Result INSERT INTO womenscores(id,name,score,time,round,game)values('-','Mary','E','7:21 AM','','WOMEN'S') – Morry Jul 30 '15 at 20:22
ie. after str_replace it is absent but appears after? – splash58 Jul 30 '15 at 20:26
https://eval.in/409202 set input data there and look result. I am ready to improve the code if error will be – splash58 Jul 30 '15 at 20:31
Sorry, I use $ar = str_replace("'", '', $ar); in the while looop, and I print out the $ar and it's fine & Apostrophe is removed .....But when I do my inserts using $ar[0]...$ar[5]... The Apostrophe appears. – Morry Jul 30 '15 at 20:33

How to use Str_Replace on Array Items in mySQL

2 Answers2