php variable in MYSQL query

@PeuplarchieApr 29.2010

Good day to you all,
I’m working on a script which would read a url var and sort mysql table by the variable, but I always get the errore that :

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in DB_API/sort_column.php on line 13

Can somebody help me fix my problem. I think it’s the variable that is not use right in the MYSQL query (

[code=php]$result = mysql_query(“SELECT * FROM NHL_GBG_PLAYERS ORDER BY {$col}”);[/code]

)

Here is my code :

[code=php] <?php $con = mysql_connect(“localhost”,”sports”,”sports”); if (!$con) { die(‘Could not connect: ‘ . mysql_error()); }

mysql_select_db(“sports”, $con);

$col = $_POST[‘coll’]; $result = mysql_query(“SELECT * FROM players ORDER BY {$col}”);

while($row = mysql_fetch_array($result)) {

echo “<div style=”width:1090px; align-left:auto; margin-right:auto;”>”; echo “<div style=”float:left; width:30px; border:1px solid #000000; “>” . $row[‘id’] . “</div>”; echo “<div style=”float:left; width:100px; border:1px solid #cccccc;”>” . $row[‘date’] . “</div>”; echo “<div style=”float:left; width:100px; border:1px solid #cccccc;”>” . $row[‘heure’] . “</div>”; echo “<div style=”float:left; width:100px; border:1px solid #cccccc;”>” . $row[‘lieu’] . “</div>”; echo “<div style=”float:left; width:100px; border:1px solid #cccccc;”>” . $row[‘assistance’] . “</div>”; echo “<div style=”float:left; width:100px; border:1px solid #cccccc;”>” . $row[‘adversaire’] . “</div>”; echo “<div style=”float:left; width:100px; border:1px solid #cccccc;”>” . $row[‘equipe’] . “</div>”; echo “<div style=”float:left; width:100px; border:1px solid #cccccc;”>” . $row[‘nom’] . “</div>”; echo “<div style=”float:left; width:20px; border:1px solid #cccccc;”>” . $row[‘b’] . “</div>”; echo “<div style=”float:left; width:20px; border:1px solid #cccccc;”>” . $row[‘p’] . “</div>”; echo “<div style=”float:left; width:40px; border:1px solid #cccccc;”>” . $row[‘pts’] . “</div>”; echo “<div style=”float:left; width:20px; border:1px solid #cccccc;”>” . $row[‘l’] . “</div>”; echo “<div style=”float:left; width:40px; border:1px solid #cccccc;”>” . $row[‘mdp’] . “</div>”; echo “<div style=”float:left; width:30px; border:1px solid #cccccc;”>” . $row[‘bg’] . “</div>”; echo “<div style=”float:left; width:30px; border:1px solid #cccccc;”>” . $row[‘be’] . “</div>”; echo “<div style=”float:left; width:40px; border:1px solid #cccccc;”>” . $row[‘bea’] . “</div>”; echo “<div style=”float:left; width:40px; border:1px solid #cccccc;”>” . $row[‘bed’] . “</div>”; echo “<div style=”float:left; width:40px; border:1px solid #cccccc;”>” . $row[‘PM’] . “</div>”; echo “</div><br>”;

}

mysql_close($con); ?>

[/code]

Thanks!

to post a comment

PHP

10 Comments(s) _↴

@MindzaiApr 29.2010 — #What is the value of $_POST['coll']? Did you mean $_POST['col']?

A few points:

You don't need the braces {} around the variable.

Your script is currently open to SQL injection attacks.

Why aren't you using a table to display tabular data?

@kburnikMay 02.2010 — #I think this would do the work:

may need debugging but the idea is that counts ;-)

[code=php]
 $col = $_POST['col'];
 
 
 // make sure that the the client side sent a legit field
 $fields_query="select * from players limit 1;";
 $resource = mysql_query($fields_query);
 $fields_of_table = array_keys(mysql_fetch_array($resource, MYSQL_ASSOC)); // this will list all the fields of your table (if there is data offcourse)
 if (!in_array($col,$fields_of_table)) $col = 'id'; // if sent col not exists, use first column OR default sorting field e.g. 'date' or 'ID' or smth...
 $direction = "asc";
 //
 
 $query = "select * from players order by {$col} {$direction}";
 $result = mysql_query($query);
 
 
 // as for the output... come on man... this is what you need: (although you shouldn't use div elements for tabular data...)
 $row_widths = array(30,100,20,50,30,40,...); // just add the values
 $colors = array("000000","cccccc","ccccccc",...) // just add the values
 
 
 while($row = mysql_fetch_array($result)) {
 $i=0;
 $out .= "<div style="width:1090px; align-left:auto; margin-right:auto;">";
 foreach ($row as $field_name => $data) {
 $out .=   "<div style="float:left; width:{$row_widths[$i]}px; border:1px solid #{$colors[$i]};">{$data}</div>"; 
 $i++;
 }
 $out. ="</div><br />";
 }
 
 echo $out;
 [/code]

@PeuplarchieauthorMay 02.2010 — #Can you explain more about injection attack ?

@kburnikMay 02.2010 — #here's a simple example that most beginners don't realize is very open to intrusion... let's say we are trying to login with a simple login form:

all users are stored in a database table called users

[code=html]
 <form method="post" action="login.php">
 <input type="text" name="username" />
 <input type="password" name="password" />
 <input type="submit" name="sbm" value="login" />
 </form>
 [/code]

And here's the PHP script that will handle the login request:

[code=php]
 include("my.db.connection.php");
 
 extract($_POST); // will create vars from the $_POST array, see more on php.net
 
 $query ="select count(*) c from users where username='{$username}' and password = '{$password}' ";
 
 $res = mysql_query($query);
 
 $loggedin = mysql_num_rows($res) > 0;
 
 if ($loggedin) {
 echo "Hi man and welcome!";
 } else {
 echo "Goodbye, bad credentials";
 }
 
 
 [/code]

Can you see the potential weakness of this script?

The goal is offcourse not to let a user log in with wrong credentials...

However, what if a user would type in "chucknorris" as the username and

this as his password:

[B]' or '1'='1[/B]

Well, the $query would than be:

[CODE]select count(*) c from users 
 where 
 username='chucknorris' 
 and password = ''
 or '1'='1'
 [/CODE]

Do you see what this query would do?

It would return the number of rows in the users table,

and what does the next statement check?

[code=php]$loggedin = mysql_num_rows($res) > 0;[/code]

You can be sure that the user gets logged in now :-)

In some special cases, a hacker can even drop your entire database or read data that is very confidential... so think about these introusions...

I do and have come up with following solution.

I use two functions to make sure none of these attack happen:

1)

[code=php]$id= intval($_GET['id']);
 $res = mysql_query("select * from users where id_user = $id");
 // ... $id will surely be a number no matter what is passed, if something bad happends, then $id = 0 and you can act upon it very simply...
 
 [/code]

[code=php]
 $username = secure($username);
 // or
 $data =array_map("stripslashes", $data);
 $data =array_map("secure", $_POST);
 
 extract($data);
 $res = mysql_query("insert into users (username) values ('$username')");
 // you can be sure this will work as itended
 
 // where secure is the following function.
 function secure($value,$strip_tags=false,$strip_slashes=false) { // MySQL preinsert/preupdate secure
 if (is_array($value)) return $value;
 $value=str_replace("'","''",$value);
 if ($strip_tags) $value=strip_tags($value);
 if ($strip_slashes) $value=stripslashes($value);
 return $value;
 }
 
 // The function by default just makes the single qoutes appear two times in a row, so something like 'this' would be ''this'' or 3 of these ''' would be 6 of these ''''''. And mysql does the escaping thru this double single quotes so that's a good way... 
 
 // as you can see, you can use this function to strip tags and slashes also, it's a very common task so i use this often...
 [/code]

I hope this helps to understand the injection part ?

@PeuplarchieauthorMay 02.2010 — #Sorry kburnik , i only partially understand you injection.

1) How would yu know how user login into my portal, I've only showed you the code without the loading part ?

2) You used a login code as example, but i'm talking about variable in mysql query.

The other piece of code you gave me earlier is very nice, how I could generate a row, on viwing only, that would have the option for each row, ex: delete, edit.... ?

Thanks

@kburnikMay 02.2010 — #I don't know anything about your portal, but there are only a few ways to deal with the logging in and out... And someone that wishes to hack into a site would try the sample I just gave you and even more... he would try to inject something through the fields of input or the _GET query... This is only general speaking...

My example also uses a variable, well 2 of them: $username and $password which are both extracted from $_POST. And both of those are open to injection because they're not secured... This is a sample which gives you an insight how you should protect your variables before injecting them into a mysql query.

I'm not sure about the third part... You could generate any option you like...

Just for the record, I never use this method to output... I rather use a template like:

<tr><td>{name}</td><td>{surname}</td></tr>

and then apply mysql results to the template iteratively...

That's a good way to avoid writing loops where not necessary.

I mean, it's pretty dull to write a loop each time you just want to display a table...

@PeuplarchieauthorMay 02.2010 — #Ok I see your point for the injection. Thanks a lot for this knownledge sharing ! Appreciated !

CAn you give me a example on how you do display it ?

@kburnikMay 02.2010 — #Here's a simple table being displayed:

[code=php]
  $data=sqlarr("
 select
 v.ip as ip,
 date_format(v.regdate,'&#37;d.%m.%Y. %H:%i:%s') as regdate,
 concat(round((unix_timestamp(now())-unix_timestamp(v.lastaction)) / 60),' min') as lastaction
 from 
 visitors v
 where
 v.human=1
 and id_user=0
 and time_to_sec(timediff(now(),v.lastaction))<=12000
 order by 
 v.lastaction desc
 limit 30
 ;
 ");
 
 if (count($data)) { 
 $header="
 <tr>
 <th>IP</th>
 <th>Dr&#382;ava</th>
 <th>Vrijeme posjeta</th>
 <th>Zadnja aktivnost</th>
 </tr>
 ";
 $template="
 <tr class='row%#mod2%'>
 <td><strong>%ip%</strong></td>
 <td>%country%</td>
 <td>%regdate%</td>
 <td>%lastaction%</td>
 </tr>
 ";
 
   // fill in with country man ;)
   foreach ($data as $index=>$row) {
   $data[$index]['country']=$_geoip->country_by_ip($row['ip'],'short3');
   }
 
   $body=sd($data,$template);
   $table="
   <table class='table'>
   <thead>$header</thead>
   <tbody>$body</tbody>
   </table>
   ";
   $out.="<strong>Posjetitelji online</strong>:<br /><br />";
   $out.=$table;
  } else {
   $out.=success("Nema posjetitelja online.",false);
  }
 
 echo $out;
 [/code]

@katierosyMay 04.2010 — #The error indicates that the error is on the sql line. It is better if you read a good article on sql injection from net.

@katierosyMay 05.2010 — #This may work, please check

<?

$col = $_GET['col'];

$result = mysql_query("SELECT * FROM NHL_GBG_PLAYERS ORDER BY '$col'");

?>

Also in #PHP _↴

error handling Correct use of $_POST store images in file and recall

Success!

Help @Peuplarchie spread the word by sharing this article on Twitter...

Tweet This

php variable in MYSQL query

10 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

php variable in MYSQL query

10 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

10 Comments(s) _↴

Also in #PHP _↴