0

DESCRIPTION

I want to be able to eliminate special characters in mysql. I WAS using mysql_real_escape_string but I was told it was not correct, and soon to be discontinued

Question

What is an alternative for using mysql_real_escape_string in my case below? Or is there another approach?

ISSUE

I am parsing CSV form URL, and creating INSERT statements. In my case I'm reading in data from an CSV via URL, examples online don't take into account $f_pointer.

Here is an example of one of data lines to insert:

Note: the ' in Green's Mill is causing the issue.

INSERT INTO scores(place,person,score,grade,round,location)values('-','Alex','2','5','','Green's Mill')

My Failed Approach for solving the ISSUE:

I was using mysql_real_escape_string - clearly badly

SCRIPT - Parse Data & Create Insert

 <?php

$servername = "localhost";  
$username = "root";
$password = "XXXX";
$dbname = "player";

 // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);

        $f_pointer=fopen("compress.zlib://https://www.url.com/api/v2/projects/xxxx/last_ready_run/data?api_key=xxx&format=csv","r");

 $sql = "TRUNCATE TABLE scores";
 $conn->query($sql);



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

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

;
$sql="INSERT INTO    scores(place,person,score,grade,round,location)values('$ar[0]','$ar[1]','$ar[2]','$ar[3]','$ar[4]','$ar[5]')";
 $conn->query($sql); 
  echo $sql;
 echo "<br>";


   }
?>

OUTPUT RESULTS

Fails to insert because of the special character ' in the location field.

DESIRED RESULTS

INSERT INTO scores(place,person,score,grade,round,location)values('-','Alex','2','5','','Greens Mill')

Note: Green's Mill would needs to be Greens Mill without the '.

chris85
  • 23,846
  • 7
  • 34
  • 51
Sarah Boland
  • 87
  • 1
  • 12
  • 2
    While you applied a lot of effort into creating a question, which is appreciated, try to keep questions quick and to the point. We're not submitting term papers here or a ticket to a help desk, so it can be more casual. As to your question, if you're using `mysqli`, the answer is in [the documentation on prepared statements](http://php.net/manual/en/mysqli.prepare.php). Try using those and this won't be an issue. – tadman Jul 30 '15 at 15:55
  • if you want to avoid apostrophes or any other character(s), just replace them before going into db. There are many functions that will do this, such as `str_replace` and `preg_replace` just to name a few, since you want to avoid using escaping functions. Your question could have easily been cut down to saying: *"I have this data containing these characters, and I want to eliminate those characters from being inserted into my database"*. – Funk Forty Niner Jul 30 '15 at 16:30
  • @tadman OP deleted her other question earlier http://stackoverflow.com/q/31726293/ and I closed it due to mixing APIs (see comments in there too). Now this question was closed using the "How to avoid injection" link, yet the goal here is that she wants an alternative to escaping functions. I posted a comment above to that effect. I for one, don't know why she doesn't use them, but hey... there's always a method to one's madness right? ;-) – Funk Forty Niner Jul 30 '15 at 16:41
  • @Fred-ii- I don't see where avoiding the escaping functions is specified, only avoiding the deprecated `mysql_real_escape_string` function, which is a noble goal here. – tadman Jul 30 '15 at 16:44
  • @tadman the title says *"Alternative for mysql_real_escape_string in mysql"* well we know she probably meant the `i` version. Then her comment in the other question *"I want to be able to eliminate special characters when inserting them into my SQL table using WITHOUT using mysql_real_escape_string."* - I don't know where to throw myself here; in her arms or off a bridge (lol). *Baffled*. Anyway, I told her she had to use `mysqli_real_escape_string()` and how to use it and a link to the function. Where we are with this question, I've no idea. If clues were shoes right now, I'd be barefoot. – Funk Forty Niner Jul 30 '15 at 16:49
  • I'd argue prepared statements qualifies as "without using" and is still sensible. In any case, the original question reads as "without using [the deprecated API]" as far as I can tell. – tadman Jul 30 '15 at 16:51
  • @tadman I doubt it. OP's going to have to step in and prove one of us, or both of us wrong. At the bottom of her question, she wrote: *"Note: Green's Mill would needs to be Greens Mill without the `'`"* - Escaping doesn't get rid of the apostrophes, they only "allow them". – Funk Forty Niner Jul 30 '15 at 16:54
  • Im using fgetcsv(). str_replace and preg_replace would require myself writing a lengthly parser. Sadly that's not an option for me. thanks tho – Sarah Boland Jul 30 '15 at 18:28

0 Answers0