11 Replies - 3193 Views - Last Post: 07 August 2013 - 09:14 AM Rate Topic: -----

#1 golla   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 31-July 13

Error Query string too long ?

Posted 31 July 2013 - 07:00 AM

i have a problem with mod_security that is installed on my server,

when i try to DELETE/Update somthing on my detabase i get this error:
Forbidden 

 You don't have permission to access /manager/index.php on this server. 

 Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.


so they (the hosting company) told me to Shorten my QUERY STRING to less then a 1000, that for every action i make it writes a log.
Because its above 1000 and the mod_s is blocking it...how do i do that ? (Shorten my QUERY STRING) and how do i check what is Defined in the QUERY STRING as the limit ?

1. untill now it deleted just fine, what Suddenly happend ? i didnt Touched the code for a month and it happend about a week ago...

2. my hosting company sent me this error log: there are 2 things i dint understand in it :

this is just a part of it:
DeleteId%5B%5D=790&ChangeUp=%D7%9E%D7%A2%D7%95%D7%93%D7%9B%D7%9F&DeleteId%5B%5D=665&DeleteId%5B%5D=366&DeleteId%5B%5D=365&DeleteId%5B%5D=364&DeleteId%5B%5D=362&DeleteId%5B%5D=363&DeleteId%5B%5D=396&DeleteId%5B%5D=397&DeleteId%5B%5D=398&DeleteId%5B%5D=399&DeleteId%5B%5D=400&DeleteId%5B%5D=404&DeleteId%5B%5D=403&DeleteId%5B%5D=619&DeleteId%5B%5D=620&DeleteId%5B%5D=621&DeleteId%5B%5D=622&DeleteId%5B%5D=623&DeleteId%5B%5D=624&DeleteId%5B%5D=718&DeleteId%5B%5D=554&DeleteId%5B%5D=613&DeleteId%5B%5D=614&DeleteId%5B%5D=651&DeleteId%5B%5D=1081&DeleteId%5B%5D=1082&DeleteId%5B%5D=1083&DeleteId%


1. when i try to delete 1 row it trys to delete all my date ?
2. or is it Because i have over a 1000 id's so the search for the right one make it stuck ?

this is the delete:
<?php 
    if (isset($_POST["deleteParty"]))  { 
       $id = $_POST["IdForChange"]; 
       $link = mysql_connect("localhost",'pn_manger',*****) or die(mysql_error()); 
       mysql_select_db("pn_parties",$link)or die(mysql_error()); 
       $query="DELETE FROM party WHERE Id = $id"; 
       $result=mysql_query($query,$link)or die(mysql_error()); 
       mysql_close() or die(mysql_error()); 
    } 
 ?>


If u need somthing else tell me and ill post

thank you.

Is This A Good Question/Topic? 0
  • +

Replies To: Error Query string too long ?

#2 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Error Query string too long ?

Posted 31 July 2013 - 07:43 AM

What does the URL in the browser look like when you go to that page? - The "query string" is the GET part of the URL, the part after the question mark and before the hash tag, so if it is going above a 1000 chars, you'd definitely notice that in the browser.

Quote

1. when i try to delete 1 row it trys to delete all my date ?

By the looks of that query string excerpt, I'd guess it's trying to delete a lot more than one row. It's passing a number of different IDs.

Quote

2. or is it Because i have over a 1000 id's so the search for the right one make it stuck ?

You'd have to go a whole hell of a lot higher than 1000 IDs for MySQL to get stuck searching. - Try somewhere in the hundreds of millions range, if not billions on a properly configured server.


Now, with that said, I'll move on to the more pressing issue: The utter lack of security in that script, and the ease with which it could be used to destroy your "party" table.

Imagine if I (or any amateur script kiddie that comes your way) were to pass this as the POST data:
deleteParty=1&IdForChange=1+OR+Id+%3E+0


To make it clearer, this POST data string would result in these values in your PHP code:
$_POST["deleteParty"] = "1";
$_POST["IdForChange"] = "1 OR Id > 0";


Which would lead to your DELETE query reading like this:
DELETE FROM party WHERE Id = 1 OR Id > 0



Do you see the problem?

I suggest you read up on SQL Injection, and then move on to the article on Choosing an API for your database interactions. - Simply put: don't use the old mysql_* function, use PDO or MySQLi instead.
Was This Post Helpful? 0
  • +
  • -

#3 Slice   User is offline

  • sudo pacman -S moneyz


Reputation: 253
  • View blog
  • Posts: 761
  • Joined: 24-November 08

Re: Error Query string too long ?

Posted 31 July 2013 - 07:50 AM

Also on top of what Atli said, is $_POST["idForChange"] an integer or a string? if it's a string then you need to put single quotes around it, like:

$query="DELETE FROM party WHERE Id = '$id'"; 


Was This Post Helpful? 0
  • +
  • -

#4 nandureddy   User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 129
  • Joined: 31-January 11

Re: Error Query string too long ?

Posted 31 July 2013 - 07:52 AM

Try changing line 4 and 5 to:
$link = mysql_connect('localhost','pn_manger','*****') or die(mysql_error());
mysql_select_db('pn_parties',$link)or die(mysql_error());

Changing double quotes to single quotes.
Was This Post Helpful? 0
  • +
  • -

#5 VolcomMky   User is offline

  • D.I.C Regular

Reputation: 74
  • View blog
  • Posts: 315
  • Joined: 13-May 09

Re: Error Query string too long ?

Posted 31 July 2013 - 07:59 AM

First thing I noticed was that your query doesn't mention the "DeleteId" array from the form.

This is the decoded version of your post
DeleteId[]=790&ChangeUp=žע•“›Ÿ&DeleteId[]=665&DeleteId[]=366&DeleteId[]=365&DeleteId[]=364&DeleteId[]=362&DeleteId[]=363&DeleteId[]=396&DeleteId[]=397&DeleteId[]=398&DeleteId[]=399&DeleteId[]=400&DeleteId[]=404&DeleteId[]=403&DeleteId[]=619&DeleteId[]=620&DeleteId[]=621&DeleteId[]=622&DeleteId[]=623&DeleteId[]=624&DeleteId[]=718&DeleteId[]=554&DeleteId[]=613&DeleteId[]=614&DeleteId[]=651&DeleteId[]=1081&DeleteId[]=1082&DeleteId[]=1083&DeleteId%


So it looks like your using checkboxes on a form that enables mass delete.

So I wrote this to see if it would help,
if(is_array($_POST['DeleteId']))
{
	if(count($_POST['DeleteId']) == 1)
	{
		$sqlWhere = 'Id = ' . $_POST['DeleteId'][0];
	}
	else
	{
		$sqlWhere = implode(' OR Id=',$_POST['DeleteId']);	
	}
}
else
{
	$sqlWhere = 'Id = ' . $_POST['DeleteId'];
}


But then realized, if your getting a query too long error. 1000 seems really short, other systems like SugarCRM build quite large sql queries.

What probably happened was your hosting company did a update and changed their server settings, thus not being friendly with your operation

Have you tried echoing out your sql query and/or $_POST data to try and debug to find out why all of your records are being deleted?
Was This Post Helpful? 0
  • +
  • -

#6 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Error Query string too long ?

Posted 31 July 2013 - 08:01 AM

nandureddy said:

Changing double quotes to single quotes.

That doesn't really matter. Unless there are variables inside the strings, or if you are trying to use certain escaped chars, then double quotes and single quotes work exactly the same. If all there is is plain text, then you can use them interchangeably. - The string documentation explains the differences pretty thoroughly.
Was This Post Helpful? 0
  • +
  • -

#7 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Error Query string too long ?

Posted 31 July 2013 - 08:12 AM

View PostVolcomMky, on 31 July 2013 - 02:59 PM, said:

$sqlWhere = implode(' OR Id=',$_POST['DeleteId']);	


You can optimize this a bit by using the IN clause instead. It will be especially important if the query string length is a factor.
$sqlWhere = "Id IN(" . implode(",", $listOfIds) . ")";



Of course, the IDs will still need to be validated and sanitized first, or the code remains wide open to SQL Injection.

View PostVolcomMky, on 31 July 2013 - 02:59 PM, said:

But then realized, if your getting a query too long error. 1000 seems really short, other systems like SugarCRM build quite large sql queries.

What probably happened was your hosting company did a update and changed their server settings, thus not being friendly with your operation

If this is in fact the problem, then using a more modern database API could be the solution. Using a prepared statement to execute individual delete statements for each ID (or in small groups, if you prefer), wrapped in a transaction, would quickly and safely delete everything without going anywhere near any query length limitations.
Was This Post Helpful? 0
  • +
  • -

#8 VolcomMky   User is offline

  • D.I.C Regular

Reputation: 74
  • View blog
  • Posts: 315
  • Joined: 13-May 09

Re: Error Query string too long ?

Posted 31 July 2013 - 08:25 AM

You are absolutely right @Atli, I keep forgetting about the IN Sql operation.

And yes, it would still be open to Sql Injection

You can use something like this, but I would also recommend moving onto PDO or something similar.

<?php
function all_ints($array)
{
	foreach($array as $id)
	{
		$int = (is_numeric($id)) ? true : false;
		if(!$int)
		{
			return false;	
		}
	}
	return true;
}

if(all_ints($_POST['DeleteId']))
{
	// Run Query	
	if(is_array($_POST['DeleteId']))
	{
		if(count($_POST['DeleteId']) == 1)
		{
			$sqlWhere = 'Id = ' . $_POST['DeleteId'][0];
		}
		else
		{
			$sqlWhere = 'Id IN(' . implode(',',$_POST['DeleteId']) . ')';	
		}
	}
	else
	{
		$sqlWhere = 'Id = ' . $_POST['DeleteId'];
	}
}
else
{
	// Stupid injection attempts...	
}
?>

Was This Post Helpful? 0
  • +
  • -

#9 golla   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 31-July 13

Re: Error Query string too long ?

Posted 31 July 2013 - 08:25 AM

Yes i do use a checkboxes on a form that enables mass delete, dont know if u need this but this is the code i use:

<?php
    if (isset($_POST["deleteMany"]))  {
		$parties_id = $_POST["DeleteId"];
		$party_number = count($parties_id);
		$good_ids_numbers=array();
		for($j=0; $j<$party_number; $j++){
			if(substr($parties_id[$j],0,6) == "delete"){
				array_push($good_ids_numbers,$j);
			}
		}
		/*
		here get rid of id that are not begining with good(look a class_change for understaning)
		*/
		$link = mysql_connect("localhost",'pn_manger','*****') or die(mysql_error());
		mysql_select_db("pn_parties",$link)or die(mysql_error());
		if (! empty($parties_id)) {
			for($i=0; $i < $party_number; $i++)
			{
				$party_id = (int)substr($parties_id[ $good_ids_numbers[$i] ],8);
				$query="DELETE FROM party WHERE Id = $party_id";
				$result=mysql_query($query,$link)or die(mysql_error());
			}
		}
		mysql_close() or die(mysql_error());
	}
	
?>


What is still dont understand is this, the hosting company told me that the "MOD_SECURITY" was since ever...its not somthing new, and i didnt edited my code for over a month, so what happend all of a sudden that messed it up like it ?
Was This Post Helpful? 0
  • +
  • -

#10 VolcomMky   User is offline

  • D.I.C Regular

Reputation: 74
  • View blog
  • Posts: 315
  • Joined: 13-May 09

Re: Error Query string too long ?

Posted 31 July 2013 - 08:33 AM

I honestly don't see how that code could work.

This code doesn't really match the query string that your posting because its checking the value of the DeleteId's to see if they contain the word "delete" which they dont because they are ID's
$parties_id = $_POST["DeleteId"];
		$party_number = count($parties_id);
		$good_ids_numbers=array();
		for($j=0; $j<$party_number; $j++){
			if(substr($parties_id[$j],0,6) == "delete"){
				array_push($good_ids_numbers,$j);
			}
		}


And you are building a array of ID's off of a array of ID's so it looks like your just moving or trying to move 1 array to another array

This post has been edited by VolcomMky: 31 July 2013 - 08:35 AM

Was This Post Helpful? 0
  • +
  • -

#11 golla   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 31-July 13

Re: Error Query string too long ?

Posted 01 August 2013 - 05:36 AM

this is what i think,
The error occurs when an action is performed on the database, but only the action of deleting and updating,

When the INSERT operation is performed there is no problem.

I realized that,the INDEX.php page pulls all the rows from the database.

and Because there are many rows displayed in the INDEX page during the action (DELETE OR UPDATE) the Error occurs.

Once I deleted all rows and left only one line for testing,the UPDATE AND DELETE worked just fine.

I mean, I guess the error is related to the amount of lines...what do you think ?
Was This Post Helpful? 0
  • +
  • -

#12 VolcomMky   User is offline

  • D.I.C Regular

Reputation: 74
  • View blog
  • Posts: 315
  • Joined: 13-May 09

Re: Error Query string too long ?

Posted 07 August 2013 - 09:14 AM

This is marking everything to be posted as deleted
<?php
echo "<td ><input name=\"DeleteId[]\" id= \"hidden$id\" value=\"$id\" type=\"hidden\" /> <input type=\"checkbox\" id= \"box$id\"  name =\"AddDelete\" value=\"מחק\" onclick=\"return class_change('$id','box$id',0)\" /></td>"."\n";
?>


Your javascript function "class_change()" changes the value of the hidden delete field, so it still gets passed to the server to be processed.

If this is the route you want to go, I recommend using the javascript to generate or remove the hidden fields, that way your $_POST doesn't get bombarded with every record you have.
<script>
function class_change(tr_id,box_id,hidden)
{
	
	var box = document.getElementById(box_id);
	if (box.checked == true ){
		document.getElementById(tr_id).setAttribute("class", "LcheckBack");   
		document.getElementById("colCon" + tr_id).innerHTML = '<input name="DeleteId[]" id="hidden'+tr_id+'" value="'+tr_id+'" type="hidden" />' + document.getElementById("colCon" + tr_id).innerHTML;
		document.getElementById("hidden" + tr_id).value = "deleteme"+document.getElementById("hidden" + tr_id).value;
	}
	else{
		if (hidden == 1) {
			document.getElementById(tr_id).setAttribute("class", "LhiddenBack");   
		}
		else{
			document.getElementById(tr_id).setAttribute("class", "LgrayBack");   
		}
		document.getElementById("colCon" + tr_id).removeChild(document.getElementById("hidden"+tr_id));
	}
	change_num_for_delete();
	return true;
}
</script>


OR, a easier fix, would be to change

This
echo "<td ><input name=\"DeleteId[]\" id= \"hidden$id\" value=\"$id\" type=\"hidden\" /> <input type=\"checkbox\" id= \"box$id\"  name =\"AddDelete\" value=\"מחק\" onclick=\"return class_change('$id','box$id',0)\" /></td>"."\n";


To This
echo "<td> <input type=\"checkbox\" id= \"box$id\"  name =\"DeleteId[]\" value=\"deleteme".$id."\" onclick=\"return class_change('$id','box$id',1)\" /></td>"."\n";


And this
function class_change(tr_id,box_id,hidden)
{
		var box = document.getElementById(box_id);
	if (box.checked == true ){
		document.getElementById(tr_id).setAttribute("class", "LcheckBack");   
		document.getElementById("hidden" + tr_id).value = "deleteme"+document.getElementById("hidden" + tr_id).value;
	}
	else{
		if (hidden == 1) {
			document.getElementById(tr_id).setAttribute("class", "LhiddenBack");   
		}
		else{
			document.getElementById(tr_id).setAttribute("class", "LgrayBack");   
		}
	}
	change_num_for_delete();
	return true;
}


To This
function class_change(tr_id,box_id,hidden)
{
		var box = document.getElementById(box_id);
	if (box.checked == true ){
		document.getElementById(tr_id).setAttribute("class", "LcheckBack");   
	}
	else{
		if (hidden == 1) {
			document.getElementById(tr_id).setAttribute("class", "LhiddenBack");   
		}
		else{
			document.getElementById(tr_id).setAttribute("class", "LgrayBack");   
		}
	}
	change_num_for_delete();
	return true;
}

This post has been edited by VolcomMky: 07 August 2013 - 09:14 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1