Page 1 of 1

Escaping apostrophe's

Posted: Tue Oct 16, 2007 10:21 am
by dwessell
Hi,

I'm working on a small script that copies a table into another table (Will do some changing of fields too)..

Some of the fields have ' in them.. Which causes a mySQL error.. I'm was hoping to do a mysql_real_escape_string on the SQL statement and be done with it.. However, that causes more errors..

The code is something like this:

Code: Select all

<?
error_reporting(6143);
$link = mysql_connect(localhost,xx,xx) or die (mysql_error());
$db   = mysql_select_db(xx,$link) or die (mysql_error());

$result = mysql_query("SELECT * FROM members",$link) or die (mysql_error());

while($row = mysql_fetch_array($result,MYSQL_ASSOC)){

$sql = 	"INSERT INTO members2 (name,address,city,state,zip,country,email,cardtype,cardnum,cardexp,cardname,username,password,billplan,promocode,lastbilled,entrydate,
	expiredate,subdate,ccattempt,cardnum_checksum) 
VALUES 
('{$row['fname']}','{$row['address']}','{$row['city']}','{$row['state']}','{$row['zip']}','{$row['country']}','{$row['email']}','{$row['cardtype']}','{$row['cardnum']}'
	,'{$row['cardexp']}','{$row['cardname']}','{$row['username']}','{$row['password']}','{$row['billplan']}','{$row['promocode']}','{$row['lastbilled']}'
	,'{$row['entrydate']}','{$row['expiredate']}','{$row['subdate']}','{$row['ccattempt']}','{$row['cardnum_checksum']}')";

$safe = mysql_real_escape_string($sql);
echo "$sql <br>";

mysql_query($safe,$link) or die (mysql_error());
	}	

?>
Will each $row variable need to be escaped seperately? Or is there a smarter way to do it all at once?

Thanks
David

Posted: Tue Oct 16, 2007 10:25 am
by John Cartwright
You need to escape every variable.

Posted: Tue Oct 16, 2007 10:36 am
by John Cartwright
To add to my oh so helpful response, I'd personally have all my queries generated for me.

implode(), array_map(), mysql_real_escape() combination would definitely shorten your code

Posted: Tue Oct 16, 2007 2:43 pm
by pickle
Depending on your setup, it might be easier to run this type of query:

Code: Select all

CREATE TABLE newTable SELECT * FROM oldTable
Then run a few more queries to modify the columns.

Posted: Tue Oct 16, 2007 3:04 pm
by d3ad1ysp0rk
I see a few potential problems, but an actual error would help..