addslashes() on xls to mysql import script

[dud-dee]

hi everybody, i want to export data from an xls file into my mysql table database. my data on the xls file are mostly consist of single quotation mark ('). this requires me to use the addslashes() in order to safely store my data into database. i tried to manipulated some one's script like this:

 

$sql .= "'".htmlentities(addslashes($data->sheets[0]['cells'][$i][$j]))."', ";

 

the result is, data successfully stored but no slashes added into the data stored.

 

can anyone help me please?

 

 

here is my entire script:

<?php

require_once 'inc/reader.php';

 

$db_host = "localhost";

$db_username = "root";

$db_password = "";

$db_name = "pilih6";

 

$data = new Spreadsheet_Excel_Reader();

 

// Set output Encoding.

$data->setOutputEncoding('CP1251');

 

// Read data

$data->read('test.xls');

 

// Show All Error

error_reporting(E_ALL ^ E_NOTICE);

 

// Connection To MySQL

$cn = mysql_connect($db_host, $db_username, $db_password);

if($cn)

{

$db = mysql_select_db($db_name);

 

if($db)

{

$success = 0;

$failed = 0;

// Exclude the first row

for($i=2;$i <= $data->sheets[0]['numRows']; $i++)

{

$sql = "INSERT INTO yunus VALUES (null,";

for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)

{

 

$sql .= "'".htmlentities(addslashes($data->sheets[0]['cells'][$i][$j]))."', ";

}

$sql = substr($sql, 0, strlen($sql) - 2);

$sql .= ")";

 

$rs = mysql_query($sql);

 

if($rs) $success++;

else $failed++;

}

 

echo "Success insert ".$success." data and failed insert ".$failed." data";

}

else

echo "Can't connect to database '".$db_name."'";

}

else

echo "Can't connect to mysql";

?>

[PFMaBiSmAd]

The \ escape characters are NOT inserted into the database. They only exist in the query itself.

 

You should also be using mysql_real_escape_string() instead of addslashes()

[dud-dee]

You should also be using mysql_real_escape_string() instead of addslashes()

 

i tried using this:

$sql .= "'".mysql_real_escape_string($data->sheets[0]['cells'][$i][$j])."', ";

 

but i got the same result. did i form it wrong?

[Mchl]

The \ escape characters are NOT inserted into the database. They only exist in the query itself.

 

In other words, what you get is what you should be getting. Nothing to worry about.

[dud-dee]

The \ escape characters are NOT inserted into the database. They only exist in the query itself.

 

In other words, what you get is what you should be getting. Nothing to worry about.

 

i need to insert the \ escape characters into the database, otherwise i will have to modify all pages i have made to display the data from the database  :'(

thank you for the responses anyway, but i would be very glad if someone out there could help me solve this problem..

[Mchl]

That's exactly what you should do. You fell victim of double escaping, probably due to gpc_magic_quotes being enabled on your server.

Use addslashes again, to emulate magic_quotes.

 

$sql .= "'".mysql_real_escape_string(addslashes($data->sheets[0]['cells'][$i][$j]))."', ";

[dud-dee]

$sql .= "'".mysql_real_escape_string(addslashes($data->sheets[0]['cells'][$i][$j]))."', ";

 

solved..

 

thank you so much.. won't forget this.