SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Hybrid View

  1. #1
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is the best/correct way to query a database with variables?

    Can someone tell me what the best way to query a database is that uses variables? I'm kind of confused about how and where to use ' and " and whether or not to escape the PHP code when loading variables and also with using globals. Here's an example of one of my queries. Can someone make some suggestions as to how to do it "correct"?

    Code:
    $result = mysql_query("SELECT login, pass, id FROM users WHERE login=\"$_POST['login']\"") or die ("Invalid query : Error 3.1");
    Is it better to type the name of each column if all are queried or is it fine to just use *? I recall reading somewhere that it's best to type them all out but I just wanted to see what you all think about this.
    John Saunders

  2. #2
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_query('SELECT login, pass, id FROM users WHERE login="'. addslashes($_POST['login']) .'"');



    And yes, never SELECT *.

  3. #3
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help. Are you using addslashes to prevent SQL injection attacks? Should this be used every time a query is run that requires a variable to be defined from a URL/form? How about the ones listed below. Do you see anything wrong with these?

    This one is loaded from a hyperlink:

    mysql_query('UPDATE catbookmarks SET parent_id="'.$category.'", name="'.$name.'" WHERE id="'.addslashes($_GET['id']).'"')

    mysql_query('INSERT INTO catbookmarks VALUES ('',"'.$_POST['category'].'","'.$_POST['name'].'")')
    John Saunders

  4. #4
    SitePoint Member Aber_Al's Avatar
    Join Date
    Dec 2004
    Location
    Wales
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bit of an aside question here - globals vs locals - is it best to query the global $_POST['login'] or enter it into a local variable?

    I thought it was best to assign to a local var in terms of performance (accessing globals each time) etc. and that $_POST at any rate will be lost on re-submission of the form so could that lead to problems later on if the pages are 'username'/access level dependant and the username hasn't been moved out of the $_POST into either the $_SESSION or something else?

    Yours an interested newbie,

    Al.

  5. #5
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is totally unnecessary to do something like
    $login = $_POST['login']
    It doesn't have any speed impact at all if you continue to use $_POST['login'] . What you were talking about is using "global" in a function definition.
    And every variable that you don't specifically assign / save / whatever will be lost after you load another page. Doesn't matter if it's $_POST['login'] or $login.

  6. #6
    SitePoint Member Aber_Al's Avatar
    Join Date
    Dec 2004
    Location
    Wales
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool thanks for clarifying - just got a bit confused after reading another thread a little while back. I've gone back and re-read it and realised I got the wrong end of the stick.

    Cheers,

    Al.

  7. #7
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    should you not use mysql_real_escape_string for data that is being stored in a database?

  8. #8
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes. That or addslashes().

  9. #9
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_escape_string() is identical to addslashes() PLUS it filters some mysql-specific stuff which addslashes() doesn't catch, as far as I know, so might as well use the database function anyway.

    As for $login = $_POST['login'], what that is doing is creating a reference to an element of a global array (in this case $_POST) so there's no speed gain whatsoever - you're still accessing the global array.

  10. #10
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Warboss_Alex
    [...] As for $login = $_POST['login'], what that is doing is creating a reference to an element of a global array (in this case $_POST) so there's no speed gain whatsoever - you're still accessing the global array.
    That is no reference. You are populating $login with the value of $_POST['login']. After that you can modify $_POST['login'] as you desire and $login won't be changed.
    And $_POST is not just global, it's superglobal. Small difference there.

  11. #11
    SitePoint Zealot
    Join Date
    May 2004
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    .. Ugh. Yeah, you're right there. :S .. I feel ashamed now.

    $login =& $_POST['login']

    That'd be a reference, right? Bugger.

    What about: $a = $_POST;

    Would that be a copy of the array or a reference?

  12. #12
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all of your replies. I'm still a little confused about how to process data before and after it is submitted to a database. I know I could look at some other scripts that I've downloaded as an example, but I'd like to know what the best/correct way is to do this.

    I'm also confused about the whole magic quotes thing and if there should be some script at the top of the file that runs through values that are submitted as POST, COOKIES, SESSIONS, etc.

    Can a PHP guru please list an example of everything that should be done to a variable (name, phone, email, etc.) from the time it is submitted in a form to when it's inserted/modified in the database and when it is retrieved to display on a site?
    John Saunders

  13. #13
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $a = $_POST - this is a copy of the $_POST superglobal

    In relation to form submitted variables (and btw, I am NOT a PHP guru), I generally do the following


    1. If get_magic_quotes_gpc is 1, then stripslashes on the superglobal arrays ($_GET, $_POST, $_COOKIES). This is so that I know what I am dealing with in the rest of the code.
    2. Validate each and every variable. What is valid is dependant of the application, e.g validate a name - what should a name contain? alpha character only? alphanumeric characters? etc. Aslo, you shoud make sure that there are no more characters in the variable than are allowed for in the database
    3. before submitting the variables to the database, use mysql_real_escape_string to escape the variables
    4. add single quotes to all string fields
    5. make sure all numeric fields are indeed numeric (intval and floatval)
    6. build the SQL and echo it to the screen for diagnostic purposes. It is amazing how many stupid errors I catch by doing this
    7. run the query and ALWAYS chect the result. If an eror has occured, I always print out the SQL and the mysql_error.

    I hope this is of some use. It seems like a lot of work, but you wouldn't believe how stupid some of the users are. they will put any old crap into your data entry fields and expect things to be ok.


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
  •