9 replies to this topic

[chad]

Hey guyz,

I have written a code that should copy certain information from a table to another table.
Below are the following codes:

$a = mysql_query("SELECT * FROM table1");
$b = mysql_num_rows($a);

echo '<p>'.$b.'</p>';

for($i=0; $i<$b; $i++)
{
	$c = mysql_fetch_array($a);
	$d = mysql_fetch_array(mysql_query("SELECT * FROM table2 WHERE user_id = ".$c['id']));
	
	if($d)
	{
		$e = mysql_query("UPDATE table2 SET user_id = '".$c['id']."',
						  title = '".$c['profile_title']."',
						  photo = '".$c['profile_photo']."',
						  privacy = '".$c['profile_privacy']."',
						  description = '".$c['profile_description']."',
						  banner_file = '".$c['banner_file']."',
						  banner_link = '".$c['banner_link']."',
						  banner_active = '".$c['banner_active']."' WHERE user_id = ".$c['id']);
		
		echo '(yes) ';
	}
	else
	{
		$e = mysql_query("INSERT INTO table2 SET user_id = '".$c['id']."',
						  title = '".$c['profile_title']."',
						  photo = '".$c['profile_photo']."',
						  privacy = '".$c['profile_privacy']."',
						  description = '".$c['profile_description']."',
						  banner_file = '".$c['banner_file']."',
						  banner_link = '".$c['banner_link']."',
						  banner_active = '".$c['banner_active']."'");
		
		echo '(<b><font color="red">no</font></b>)  ';
	}
	
	echo $i.'. '.$c['id'].'<br>';
}

echo '<p>'.$i.'</p>';

Now as you can see it copies certain fields from "table1" and put them to "table2".
The problem is that some records are being skipped and I can't seem to figure out why. When i try to echo the records as seen in the code, they output the right amount of data from the query. However when I check the database, they have copied only a certain amount of data. Can you guys help out?

The amount of records are approximately 1,223 but it only copies 1,164 records to table2.

[chad]

well i already had it solved
i had just forgotten to add php function code addslashes

i forgot that some data has single quotes with them
here's how it looks like now for those who might encounter the same problem in the future:

$a = mysql_query("SELECT * FROM user");
$b = mysql_num_rows($a);

echo '<p>'.$b.'</p>';

for($i=0; $i<$b; $i++)
{
	$c = mysql_fetch_array($a);
	$d = mysql_fetch_array(mysql_query("SELECT * FROM user_profile WHERE user_id = ".$c['id']));
	
	if($d)
	{
		$e = mysql_query("UPDATE user_profile SET user_id = ".$c['id'].",
						  title = '".addslashes($c['profile_title'])."',
						  photo = '".addslashes($c['profile_photo'])."',
						  privacy = ".$c['profile_privacy'].",
						  description = '".addslashes($c['profile_description'])."',
						  banner_file = '".addslashes($c['banner_file'])."',
						  banner_link = '".addslashes($c['banner_link'])."',
						  banner_active = ".$c['banner_active']." WHERE user_id = ".$c['id']);
		
		echo '(yes) ';
	}
	else
	{
		$e = mysql_query("INSERT INTO user_profile SET user_id = ".$c['id'].",
						  title = '".addslashes($c['profile_title'])."',
						  photo = '".addslashes($c['profile_photo'])."',
						  privacy = ".$c['profile_privacy'].",
						  description = '".addslashes($c['profile_description'])."',
						  banner_file = '".addslashes($c['banner_file'])."',
						  banner_link = '".addslashes($c['banner_link'])."',
						  banner_active = ".$c['banner_active']);
		
		echo '(<b><font color="red">no</font></b>)  ';
	}
	
	echo $i.'. '.$c['id'].'<br>';
}

echo '<p>'.$i.'</p>';

thanks for the guys who viewed anyway

[SoN9ne]

you should use mysql_real_escape_string instead....
PHP: mysql_real_escape_string - Manual

[chad]

it's not really needed
since mysql injection is not possible for that script as it does not involve any forms or any method that i know of that will enable mysql injection into it

its just a pure server side script that needs to transfer data from 1 table to another

[SoN9ne]

:closedeyes:

Obviously it was an issue hence the addslashes.... Just a suggestion because security is one area I enjoy. Addslashes would suffice for your needs but my opinion hasn't changed.

[chad]

well a real quick question
how would a hacker put a mysql injection if he has no way or form of putting one?

[SoN9ne]

They wouldn't (considering the input was properly escaped in the first place) but it's better to be safe than sorry. I never trust any data, this is where our opinions differ :). Like I said, addslashes would suffice. I tend to get a little overboard sometimes, I would use type casting also. If not only to make it easier to see what is what for other programmers working on the code. Also, I tend to be more cautious because there are always new exploits. Today your code is fine, in a year or so... who knows. Your problem was obviously single and double quotes, addslashed is perfect for that. It's just a matter of opinion that I prefer mysql_real_escape_string. There are cases where data can be inserted into the DB and it can inject other queries.I have personally experienced this about 5 years ago and have since been anal about it. Nothing personal, only an opinion. I agree its not needed but due to my past experience, I would use it. :D

[chad]

hahahah
well seeing as how you react on this case, you must have a pretty hard hit on you :P

well i know that being cautious is ok..
but well im good with it currently ^^

oh and to have you relieved a bit
that code and file was just used once and have been deleted immediately :p

[SoN9ne]

:thumbup1:

It wasn't fun.... The worst part was isolating the issue... it was a nightmare.

[chad]

hahahaha really?
sorry for that :P