Creating a MYSQL query in a loop SEE LAST POST

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

i have changed it to this but the thing is it gives no errors so the query works i have added mysql_error

this is what it gives
mysql_query("SELECT * FROM `table` WHERE `username` = 'xc' AND `profile` = 'c' ")
but when i try to do fetch rows it doesnt like this
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If mysql_query() is returning a result resource, the query executed correctly. Check mysql_num_rows() to see if it found any records. If it says zero, and you are expecting more than that, then you have a logical error in your query, not a syntactical one.
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

I have just reailsed though but when echo the query it outputs it which doesnt happen normaly i have tried it on a standerd query and it echos
Resource id #3
but my while loop query echos the full query code


here is my code

Code: Select all

$table=$_REQUEST['table'];
$i=0;
$field=array();
$result = mysql_query("select * from $table");
while ($i < mysql_num_fields($result)) {
   $meta = mysql_fetch_field($result, $i);
   if (!$meta) {
       echo "No information available<br />\n";
   }
$name=$meta->name;
$field[$i]=$_POST[$name];
$i++;

}




$searchquery='mysql_query("SELECT * FROM `$table` WHERE ';
$i=0;
while ($i < mysql_num_fields($result)) {
$meta = mysql_fetch_field($result, $i);
$name=$meta->name;
$con = ( $field[$i]=='' ) ? '"' : "`$name` = '$field[$i]'";
    $finish = ( $i == 1) ? ' ")' : ' AND ';
    $searchquery .= "$con$finish";
    $i++;
} 
 echo $searchquery;
if (!$searchquery) {
   die('Query failed: ' . mysql_error());
}


if ($searchquery) {
    echo "Searched $table";
} else {
    echo "Error Searching $table";
}





echo '<table border="1" cellspacing="0" cellpadding="0">';

while ($field=mysql_fetch_field($result)) {

echo "<th>";
echo "$field->name";
echo "</th>";
}

while ($row = mysql_fetch_row($searchquery)) {
echo "<tr>";
for ($i=0; $i<mysql_num_fields($searchquery); $i++) {
echo "<td>";
echo "$row[$i]";
echo "</td>";
}}


echo "</tr>\n";


echo "</table>";

mysql_close();

Thanks for all your help
Last edited by reecec on Tue Aug 15, 2006 6:36 am, edited 1 time in total.
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

I think this is where the problem is

Code: Select all

$searchquery='mysql_query("SELECT * FROM `bullets` WHERE ';
$i=0;
while ($i < mysql_num_fields($result)) {
$meta = mysql_fetch_field($result, $i);
$name=$meta->name;
$con = ( $field[$i]=='' ) ? '"' : "`$name` = '$field[$i]'";
    $finish = ( $i == 1) ? ' ")' : ' AND ';
    $searchquery .= "$con$finish";
    $i++;
}
 echo $searchquery;
it is treating $searchquery as words and not a Query because its in (') apostrophe

thanks reece
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I already posted about that problem.. in this thread!

viewtopic.php?p=294528#294528
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

oh yes i rember you saying sorry

so i am right its just text not a query

so how can i get this into a sql query

sorry to be a pain


thanks reece
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Code: Select all

$table=$_REQUEST['table'];
$table is assigned from user input

Code: Select all

$result = mysql_query("select * from $table");
and then used directly in query without being backticked or escaped. This is an SQL injection vulnerability.

Between these two lines escape $table with mysql_real_escape_string() to overt this vulnerability. That goes for any other variables appearing in queries.
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

Thanks


how can i fit this in my code sorry

thanks reece
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Couldn't be easier.

Code: Select all

$table = mysql_real_escape_string($table);
$result = mysql_query("select * from $table");
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You're going to need to run a query that returns whether the table exists or not. Otherwise, even with escaping, someone could wonk your script by throwing a table into it that does not exist. Then your queries won't run. Are you sure you want the user telling your script which tables to use? Can your script decide programmatically?
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

the request gets it from the URL which is sent by the script before so its like


phpfile.php?table=anytable

yes they chose the table but the script adds it to the url i need the user to choose which table but it will show there fields and they type there search term in the text boxes that relate to the table


thanks reece
User avatar
bmcewan
Forum Commoner
Posts: 55
Joined: Wed Jun 02, 2004 7:19 am
Location: West Yorkshire, UK.

Post by bmcewan »

would it not be a better idea to send that as a post request, that URL could be modified by the user and whatever is input will be put into your sql query.
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

yes it could but it will always do the same thing i will do that when the script is created for use but for development i have made it like this so i can change the table


thanks reece
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

ole wrote:Couldn't be easier.
$table = mysql_real_escape_string($table);
$result = mysql_query("select * from $table");

Thanks ole but thats not where i have the problem is i need it on the loop

my code loops round to add the values of text boxes say i Have a table and it has these fields in (eg. name, address, num)

it would loop round and do this


WHERE name = anyname
then it will loop back round adding the word AND as there is more to search
AND address= anyaddress
ect

You problably all got that from the code but my problem is this how do i change this into a sql query as at the moment it echos as text

This is the code where i have the problem

Code: Select all

$searchquery='mysql_query("SELECT * FROM `$table` WHERE ';
$i=0;
while ($i < mysql_num_fields($result)) {
$meta = mysql_fetch_field($result, $i);
$name=$meta->name;
$con = ( $field[$i]=='' ) ? '"' : "`$name` = '$field[$i]'";
    $finish = ( $i == 1) ? ' ")' : ' AND ';
    $searchquery .= "$con$finish";
    $i++;
}
 echo $searchquery;
if (!$searchquery) {
   die('Query failed: ' . mysql_error());
}
Thanks all for your help
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

OK lets get back to basics here:

Code: Select all

mysql_query(); // is a function
$variable // is a variable
= // means we are assigning something to the variable
'mysql_query("SELECT `field` FROM `table`")'; // this is not a function call,  it is the name of a function with parameter stored as a string
// as a result:
echo $variable; // will echo 'mysql_query("SELECT `x` FROM `table`")'
echo mysql_query("SELECT `field` FROM `table`"); // will call the function and output the return value from the function.

// A good way to do it
$q = 'SELECT `field` FROM `table`';
echo mysql_query($q); // will use 'SELECT `field` FROM `table`' as a database query
// this will probably output nothing of use because mysql_query only returns a result resource. See the manual http://www.php.net/mysql_query/
In your case you have a problem with this line:

Code: Select all

$searchquery='mysql_query("SELECT * FROM `$table` WHERE ';
Post Reply