$_GET adding double quotes

[jesuslover037]

Hey guys, I'm having trouble using the $_GET command. Basically, I've got an sql string that looks like this:

$typenumber = "SELECT * FROM signature WHERE school_id = ' " . $school_id . " ' ORDER BY pagenumber";

This works fine when I try to execute this sql command locally, but when I try to do so using get:

$type = $_GET['objtype'] (objtype is $typenumber passed through a url)

The code is executed like this:

SELECT * FROM signature WHERE school_id = \'schoolname\' ORDER BY pagenumber

Thus, when this code is executed, I get this very generic error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\LadderMate\main.php on line 92

I'm at a loss. ANY help would be appreciated. Thanks alot!

Austin

[greensweater]

It looks like you're not giving enough information... are you performing a mysql_real_escape_string() on the variable, including quote marks, and then sticking it into the query? Or maybe passing the whole $typenumber through mysql_real_escape_string()? I mean, the single-quotes shouldn't be escaped in this context.

Maybe you could show a little more of your script so we could pinpoint the problem.

[jesuslover037]

Sure, thanks for the reply. I'm just gonna throw in the functions that are necessary to execute the command. Just so you know, this script is basically going to allow users to click on a datatype (name, pagenumber, date, etc...) and allow them to view by that particular order. It's quite a bit of code, so if you need me to narrow it down, I'd be glad to. Please excuse my poor coding habits--I wrote most of this script before I learned how to organize code.

//DEFINE CLSMAIN CLASS INSTANCE
$objclsMain = new clsMain();

//SORT FUNCTIONS
$objclsMain->OrderLinks();
$objclsMain->GetType();

Class clsMain
{

function OrderLinks()
{
$objclsMain = new clsMain();

//SET SCHOOL_ID VARIABLE TO SCHOOL SESSION DATA
$school_id = $_SESSION[schoolid];

//CREATE SQL TYPES
$typename = "SELECT * FROM signature WHERE school_id= 'oakgrove' ORDER BY name";

$typenumber = "SELECT * FROM signature ORDER BY pagenumber";
$typecategory = "SELECT * FROM signature ORDER BY category";
$typecompleted = "SELECT * FROM signature ORDER BY completed";
$typeduedate = "SELECT * FROM signature ORDER BY duedate";
$typesent = "SELECT * FROM signature ORDER BY sent";

$objclsMain->CreateTableHeader($typename, $typenumber, $typecategory, $typecompleted, $typeduedate, $typesent);
}

function GetType()
{
$type = $_GET["objtype"];
$objclsMain = new clsMain();
$objclsMain->PopulateTable($type);

}

function PopulateTable($type)
{

$school_id = $_SESSION[schoolid];
$selectdb = "ladder";
$objclsDatabase = new clsDatabase();
$objclsMain = new clsMain();
$objclsDatabase->ConnectDB();
$objclsDatabase->SelectDB($selectdb);

	//IF THERE IS SORT TYPE INITIATED
	if(isset($_GET['objtype']))
	{
$results = mysql_query($type);


while ($row = mysql_fetch_array($results))
		{
		$pagenumber = $row['pagenumber'];
		$name = $row['name'];
		$category = $row['category'];
		$completed = $row['completed'];
		$duedate = $row['duedate'];
		$sent = $row['sent'];

	$objclsMain->CreateTableRow($pagenumber, $name, $category, $completed, $duedate, $sent);
		}
	$objclsMain->CreateTableFooter();
	}

	//IF THERE IS NO SORT TYPE PROVIDED, EXECUTE DEFAULT SORT
	else
	{
	$defaultsort = "SELECT * FROM signature WHERE school_id = '" . $school_id . "' ORDER BY pagenumber";
	$results = mysql_query($defaultsort);


		while ($row = mysql_fetch_array($results))
				{
				$pagenumber = $row['pagenumber'];
				$name = $row['name'];
				$category = $row['category'];
				$completed = $row['completed'];
				$duedate = $row['duedate'];
				$sent = $row['sent'];
			$objclsMain->CreateTableRow($pagenumber, $name, $category, $completed, $duedate, $sent);
				}
			$objclsMain->CreateTableFooter();
	}

}//END OF FUNCTION

function CreateTableHeader($typename, $typenumber, $typecategory, $typecompleted, $typeduedate, $typesent)
{

	$header =

<<<HEADER

<table border = "1" cellspacing = "3" cellpadding = "3" bgcolor = "#FFFFFF" align="left">
<tr>
	<th>Edit</th>
	<th>Delete</th>
	<th width = 10%><p><a href="main.php?objtype=$typenumber">Page #</a></p></th>
	<th width = 30%><p><a href="main.php?objtype=$typename">PageName</a></p></th>
	<th><p><a href="main.php?objtype=$typecategory">Category</a></p></th>
	<th><p><a href="main.php?objtype=$typecompleted">Completed</a></p></th>
	<th><p><a href="main.php?objtype=$typeduedate">Due Date</a></p></th>
	<th><p><a href="main.php?objtype=$typesent">Sent</a></p></th>
</tr>

HEADER;
echo $header;

}

function CreateTableRow($pagenumber, $name, $category, $completed, $duedate, $sent)
{
	$row =

<<<ROW
<tr>
<td><a href="FrmEdit.php?objnumber=$pagenumber&objname=$name&objcategory=$category&objcompleted=$completed
&objduedate=$duedate&objsent=$sent">Edit</a>
</td>
<td><a href="DeleteRecord.php?objkey=$pagenumber">Delete</a>
</td>
<td>$pagenumber</td>
<td>$name</td>
<td>$category</td>
<td>$completed</td>
<td>$duedate</td>
<td>$sent</td>
</tr>
ROW;
echo $row;
}

[Quadodo]

If Magic Quotes GPC are on then single quotes will be escaped for security reasons. What you are doing is not secure in any way. You are basically allowing someone to execute a SQL query of their liking. Don't accept SQL queries from the $_GET method! Accept data that goes into SQL queries from the GET method 😉

[jesuslover037]

Alright, thanks Quadodo. I've finally managed to fix the thing and make it more secure I think. I'm trying to learn more about security, so how exactly would someone insert their own SQL query?

Here's what I had to do to fix it: Remove 6 different strings such as
$typenumber = "SELECT FROM signature ORDER BY pagenumber";
$typecategory = "SELECT FROM signature ORDER BY category";

and move them all into one big, adaptable query:
$sort = "SELECT * FROM signature WHERE school_id = '" . $school_id . "' ORDER BY '" . $type . "'";

Now when I seem to execute it, it doesn't seem to complain that the $type is in weird /' formatting.

[bradgrafelman]

I also hope that register_globals isn't enabled, as that's yet another security exploit...

Also, you should never place user-supplied data directly into a SQL query. Instead, you should use a function such as [man]mysql_real_escape_string/man to sanitize the data first. (There are other methods of protecting yourself, e.g. casting $school_id (which I assume came from $_GET['school_id'] or something similar) to an integer before using it.

EDIT: Also, when posting PHP code please use the board's [PHP][/PHP] bbcode tags as they make your code much easier to read and analyze. You should go back now and edit your post to add these tags.

[jesuslover037]

Thanks! I'll definately check this out.