Select --- Where Xxx=''

[lynlimz]

Hi,

Yeah. Weird Title.
Alright..

Just need to ask something about mysql error routines.
If when doing a query like:

$results = mysql_query("SELECT * FROM tablename WHERE item='$value'");

What if mysql can't find the item='$value' ?
Will the follow-up codes be executed?

[tubedogg]

Yes and no. It will return an empty set and not produce an error and PHP will attempt to continue executing your script, but your script will not get far with no data to work with.

To get around this, you need to check if it's an empty set before processing it with your code. I don't have an example off the top of my head but you could probably find something in this forum about it.

[lynlimz]

Okay. Thanks
There's no mysql_error or something to report?

[New Oddity]

$results = mysql_query("SELECT * FROM tablename WHERE item='$value'");

if ($result != ""){
  //Do stuff here...
}else{
  echo "No items found containing $value";
} 

OR...

$results = mysql_query("SELECT * FROM tablename WHERE item='$value'");

if ( !isset($result) ){
  //Do stuff here...
}else{
  echo "No items found containing $value";
} 

[lynlimz]

COOL! thanks! that did it!

[lynlimz]

ARGH! It didn't! =(
Her'es my code

    $results = mysql_query("SELECT * FROM tablename WHERE filename='$add'");
    if ($results != '') {
        $comments = htmlspecialchars("$comments", ENT_QUOTES);
        $name = htmlspecialchars("$name", ENT_QUOTES);
        mysql_query("INSERT INTO tablename VALUES ( '$add', '$datetime', '$name','$comments','$REMOTE_ADDR')");
        echo '<tr><td>Your Comment has Been Added</td></tr></table></div><br><a href="comments.php?id='.$add.'">Back</a>';
    }
    else {
        echo '<tr><td>Error Encountered : File Entry does not Exist.</td></tr></table></div><br><a href="javascript:history.go(-1)">Back</a>';
        exit;
    } 

[freakysid]

From the manual http://www.php.net/manual/en/function.mysql-query.php

mysql_query() returns TRUE (non-zero) or FALSE to indicate whether or not the query succeeded. A return value of TRUE means that the query was legal and could be executed by the server. It does not indicate anything about the number of rows affected or returned. It is perfectly possible for a query to succeed but affect no rows or return no rows.

You must make the manual your friend No one can remember exaclty how every function works and I am continually discovering new functions as I takle new tasks.

As the manual says, the function mysql_query() will return 0 (which evaluates to false as does an empty string) only if the sql query was illegal or there was an error in communicating with the database. (no connection in place etc). An empty set is a valid set! The set of all records that match your criteria can be no records - that is valid.

The function mysql_num_rows($results) will tell you how many rows are in the result set.

[lynlimz]

Thanks Freakysid!
Guess what? The php Manual is better for gathering information then from the mysql documentation.

I was searching hard at the documentation at www.mysql.com

anyways, the workaround is using mysql_num_rows
If its 0 == error.

great!!! phew.
just a moment..
...from one of the comments..it read = '

Watch out when you make queries based on variables from a surfer. SELECT * FROM table where ID=$ID can easily be hacked to empty your table. Parse all variables submitted through forms/get if they are to be used in any similar situation. Of course, the hacker would need to know the name of your table.

Thats what I do! so is there any workaround to prevent this? thanks

[exbabylon]

What that quote is talking about may be true, but unless you code really loosely and have extremely sloppy queries I don't forsee that as a problem.

As for the first question/post. Here is an example:

$sql = "SELECT * FROM table WHERE column='$var' AND date > '$date'";
$result = mysql_query($sql);
if(mysql_num_rows($result) < 1){
    echo("No results.")
}else{
    while($row = mysql_fetch_array($result)){
        extract($row);
        echo("Name: $name<br>Phone: $phone<br>Date: $date");
    }
} 

Hope that helps!

God Bless

[lynlimz]

Originally posted by exbabylon
What that quote is talking about may be true, but unless you code really loosely and have extremely sloppy queries I don't forsee that as a problem.

Do you have an example of sloppy queries? Its really serious...
I might just have sloppy coding which will lead to these problems.

Originally posted by exbabylon

As for the first question/post. Here is an example:

$sql = "SELECT * FROM table WHERE column='$var' AND date > '$date'";
$result = mysql_query($sql);
if(mysql_num_rows($result) < 1){
    echo("No results.")
}else{
    while($row = mysql_fetch_array($result)){
        extract($row);
        echo("Name: $name<br>Phone: $phone<br>Date: $date");
    }
} 

Hope that helps!

God Bless

WOW! extract($row) brings out all the varibales? i always declare it one by one.

if i only want 4 out of 8 variables, should i use extract too?
Thanks

[exbabylon]

You can use extract() to pull all of the key's of an array into variables with thier values being their values (Ambigious, no?). However, it can create problems when you are dealing with a lot of variables and your column name may be the same as some already existing variables.

Take a good look at this URL:

http://php.net/manual/en/function.extract.php

As for sloppy queries I may need to just leave that as sloppy coding. For example, if I have a link like so:

<a href="display.php?query="SELECT+name+FROM+table_name">View All Entries</a>

You can imagine the PHP code to parse that and display the proper results. Now, how hard would it be for a hacker to just enter a DROP table, or something like that into the URL? Not very. However, if you take the time to use variables properly then you can bypass this problem.

That is a really far fetched cenareo, but I have seen a lot worse here in this board.

Have fun, and God Bless,

Alex Stanton

[freakysid]

I remember that we had a conversation about this a month or two ago (security of queries using user supplied data). One thing that PHP/MySQL will not allow is for multiple SQL queries to be sent using mysql_query(). I don't know who is responsible for this (whether it is PHP or MySQL) because, I can certainly execute multiple queries in the MySQL command line client. However, I remember it being discussed that some other database & scripting platforms (ahem, I think it was the Microsoft ASP/SQL Server combo) would allow this. For example I just wrote the following test program:

<?php
$value = '1; drop table TestTable;';
$dbcon = mysql_connect("localhost", "xxxxxx", "xxxxxx");
mysql_select_db("neaturl", $dbcon);
$sql = "INSERT INTO TestTable SET value = $value";
echo "<br> $sql";
$result = mysql_query($sql);
$result = (int)$result;
echo "<br>$result";
?>

And here is the output:

INSERT INTO TestTable SET value = 1; drop table TestTable;
0

So thankfully the query did not execute. I set the $value in my code, but assume that it has come from POST data or GET data. Obviouly I was expecting a number, however I didn't test to see that the value was numeric before whacking it into the SQL.

Oh yea, I just read freddydoesphp's post in another thread and it reminded me of a something. You can set up users which only have certain permissions to perform certain actions on certain databases, tables and even columns! So you can, if you want to, really tighten up what sort of access to the database the user your script is connecting to mysql as has.

[lynlimz]

So my code is alright I presume.
Since the value I'm gathering from the form is only used in the WHERE item='$item'

BTW..how do you go about creating a new user?
I don't think its possible with HostRocket?

Thank You guys!

[tubedogg]

IIRC HR uses Cpanel which means you go to Advanced > SQL Database > and find the section titled "users" and fill in the username/password box to create a new user. Then to assign that user to a database, find, under the list of your databases, the thing that looks like
User: <drop-down> Db: <drop-down> Add User to Db
and select the user you created in the user drop-down and the DB to assign it to in the DB drop down and then click the button.

[lynlimz]

Originally posted by tubedogg
IIRC HR uses Cpanel which means you go to Advanced > SQL Database > and find the section titled "users" and fill in the username/password box to create a new user. Then to assign that user to a database, find, under the list of your databases, the thing that looks like
User: <drop-down> Db: <drop-down> Add User to Db
and select the user you created in the user drop-down and the DB to assign it to in the DB drop down and then click the button.

Yes. I do know about that. But how about creating users with different levels of access? like only being restricted to modification of tables etc.?

[tubedogg]

I'm pretty sure that's not possible.

[lynlimz]

Originally posted by tubedogg
I'm pretty sure that's not possible.

lol. its alright then. not necessary i guess.