Escaping apostrophe's

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dwessell
Forum Commoner
Posts: 62
Joined: Fri Dec 23, 2005 2:30 pm

Escaping apostrophe's

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

You need to escape every variable.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

I see a few potential problems, but an actual error would help..
Post Reply