SitePoint Sponsor

User Tag List

Results 1 to 17 of 17

Hybrid View

  1. #1
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select --- Where Xxx=''

    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?
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  2. #2
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Kevin

  3. #3
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay. Thanks
    There's no mysql_error or something to report?
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  4. #4
    SitePoint Zealot New Oddity's Avatar
    Join Date
    Jun 2001
    Location
    Georgia
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

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

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

    OR...

    PHP Code:

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

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

    --Odd
    "We all live in a yellow subroutine."
    "Some call it insanity; I call it inspiration!"

  5. #5
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    COOL! thanks! that did it!
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  6. #6
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ARGH! It didn't! =(
    Her'es my code
    PHP 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;
        } 
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  9. #9
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $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
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  10. #10
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $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
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  11. #11
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    <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
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  12. #12
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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:
    Code:
    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.
    Last edited by freakysid; Jun 19, 2001 at 16:42.

  13. #13
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  14. #14
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Kevin

  15. #15
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.?
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  16. #16
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm pretty sure that's not possible.
    Kevin

  17. #17
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by tubedogg
    I'm pretty sure that's not possible.
    lol. its alright then. not necessary i guess.
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •