PHP CSV parse issue escaping strings for MySQL

Question

I'm trying to parse a number of CSV files from a product feed. I'm using the code below to grab the data from the CSV, and process row by row for insertion in to a MySQL db. For some reason every once and a while the addslashes function seems to skip the escape sequence. What am I doing wrong here?

while (($data = fgetcsv($fh, 2000, ",")) !== FALSE)
{           
    $num = count($data);
    $nl = 0; 

    for ($c=0; $c < $num; $c++)  
    {
        $nl++;  
        if ($c >= 0)
        {
            if ($nl == 1)
            {
                $Name = addslashes($data[$c]);
            }
            if ($nl == 2)
            {
                $URL = $data[$c];
            }
            if ($nl == 3)
            {
                $CatalogName = addslashes($data[$c]);
            }
            if ($nl == 4)
            {
                $LastUpdated = $data[$c];
            }
        }
    }
    if ($headerRow > 40) 
    {   
        $sql = "INSERT INTO table (name,url,catname,updated) VALUES ('$Name','$URL','$CatalogName','$LastUpdated')";
                mysqli_query($connection3,$sql) or die("Can't execute query I001.);
    }
}

Either use parametrized queries, or use mysqli_real_escape_string — Barmar
– Barmar, Commented Nov 3, 2013 at 18:35
Why are you injecting values into the SQL query if you're using MySQLi? Use prepared statements/bind variables — Mark Baker
– Mark Baker, Commented Nov 3, 2013 at 18:35
I also don't understand why you're using a for loop. Just do $Name = addslashes($data[1]); $URL = $data[2]; etc. — Barmar
– Barmar, Commented Nov 3, 2013 at 18:38
Please fix your code indentation. It looks like the query is inside the for loop. — Barmar
– Barmar, Commented Nov 3, 2013 at 18:39
The escapes are not stored in the DB, they're just used to make the SQL query parse correctly. — Barmar
– Barmar, Commented Nov 3, 2013 at 18:45

Snowburnt · Accepted Answer · 2013-11-03 19:15:21Z

1

for a parameterized query (http://php.net/manual/en/mysqli.prepare.php):

$sql=$connection3->prepare("INSERT INTO table (name,url,catname,updated) VALUES (?,?,?,?)");
$sql->bind_param('ssss',$Name,$URL,$CatalogName,$LastUpdated);
$results=$sql->execute(); //results contains whether or not the execute was successful.

While this is "Object oriented style" the actual functionality of this statement will work whether or not you prefer "objects" to "procedural style", it's all in the style. In any case, it will work and there are procedural examples in the docs.

in fact, here's how you do it procedurally:

$stmt=mysqli_prepare($connection3, "INSERT INTO table (name,url,catname,updated) VALUES (?,?,?,?)");
mysqli_stmt_bind_param($stmt, "ssss", $Name,$URL,$CatalogName,$LastUpdated);
mysqli_stmt_execute($stmt);

Now you don't have to worry about escaping your statement, but you still do have to sanitize your entries to prevent cross site scripting and other security risks.

answered Nov 3, 2013 at 19:15

Snowburnt

6,9558 gold badges33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert82 Over a year ago

Would I include all 3 statements on every iteration of the loop, or just do the first line once outside the loop, and the last two statements on every iteration?

Snowburnt Over a year ago

You can set up the prepare outside the loop then bind the parameters and execute inside the loop

Collectives™ on Stack Overflow

PHP CSV parse issue escaping strings for MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related