SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Which SELECT query is correct??

    Below are two SELECT queries that both seem to work, however I am unsure which one is more syntactically correct?!

    SELECT #1:
    Code:
          $sql = "SELECT seminar_name, venue, address1, address2, city, state, " .
                  "zipcode, telephone, listed_times, venue_image, map_image  " .
                  "FROM seminars " .
                  "WHERE (seminar_id = " . mysqli_real_escape_string($dbc, $seminar_id) . ") LIMIT 1";

    SELECT #2:
    Code:
          $sql = "SELECT seminar_name, venue, address1, address2, city, state, " .
                  "zipcode, telephone, listed_times, venue_image, map_image  " .
                  "FROM seminars " .
                  "WHERE (seminar_id = '" . $seminar_id . "') LIMIT 1";
    seminar_id is always an Integer and has been checked for correctness before this query.

    So really my confusion is on whether you can/should use mysqli_real_escape_string on a number??

    Thanks,


    Amy

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by amy.damnit View Post
    So really my confusion is on whether you can/should use mysqli_real_escape_string on a number??
    again with the php question in the mysql forum

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    again with the php question in the mysql forum

    Sorry, but I'm getting confused between the lines of PHP and MySQL forums...

    Since this is a function dealing with MySQL, I thought this was the correct place to ask.

    (On most Boards, I could ask that question in either forum.)

    Can you or a Mod move this to the PHP forum if that is where it should go?

    Sorry,


    Amy

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So really my confusion is on whether you can/should use mysqli_real_escape_string on a number??
    You can and you should.

  5. #5
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by risoknop View Post
    You can and you should.
    Glad I asked!!!

    mysqli_real_escape_string it is, then!

    Thanks,


    Amy

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    If your absolutely certain that the variable is an integer then there isn't any need to escape it. However, if your assuming its an integer then it should be escaped. You can on lt be absolutely certain that its an integer if some type of previous data cleansing occurred. At which point it isn't necessary to escape it.

    PHP Code:
    $id 8// not user controlled - injection impossible
    $sql "SELCT whatever FROM table WHERE something = $id"
    PHP Code:
    $id mysql_real_escape_string($_GET['id']); // user controlled - injection possible
    $sql "SELCT whatever FROM table WHERE something = $id"
    PHP Code:
    $id is_numeric($_GET['id'])?$_GET['id']:null// anything other then a numeric value will be null - injection impossible
    if($id !== null) {
         
    $sql "SELCT whatever FROM table WHERE something = $id";


  7. #7
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    If your absolutely certain that the variable is an integer then there isn't any need to escape it. However, if your assuming its an integer then it should be escaped. You can on lt be absolutely certain that its an integer if some type of previous data cleansing occurred. At which point it isn't necessary to escape it.

    PHP Code:
    $id 8// not user controlled - injection impossible
    $sql "SELCT whatever FROM table WHERE something = $id"
    PHP Code:
    $id mysql_real_escape_string($_GET['id']); // user controlled - injection possible
    $sql "SELCT whatever FROM table WHERE something = $id"
    PHP Code:
    $id is_numeric($_GET['id'])?$_GET['id']:null// anything other then a numeric value will be null - injection impossible
    if($id !== null) {
         
    $sql "SELCT whatever FROM table WHERE something = $id";


    Well, here is the code I am using...
    Code:
          // Get and Sanitize seminar_id.
          $seminar_id = filter_var($_GET['seminar_id'],FILTER_VALIDATE_INT);
    
          // Ensure seminar_id is Integer.
          if (is_int($seminar_id)) {
              // Valid URL value.
    
              // Path to DB Connection Script.
              require_once (MYSQL);
    
              // Build query.
          $sql = "SELECT seminar_name, venue, address1, address2, city, state, " .
                  "zipcode, telephone, listed_times, venue_image, map_image  " .
                  "FROM seminars " .
                  "WHERE (seminar_id = " . mysqli_real_escape_string($dbc, $seminar_id) . ") LIMIT 1";


    Amy

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,042
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    For your purposes below is how I would write it.

    PHP Code:
    $seminar_id = (int) isset($_GET['seminar_id']) && is_numeric($_GET['seminar_id'])?$_GET['seminar_id']:0;

    if(
    $seminar_id) {
        require_once(
    $file);
        
        
    $sql 
        
    "SELECT
              seminar_name
              ,venue
              ,address1
              ,address2
              ,city
              ,state
              ,zipcode
              ,telephone
              ,listed_times
              ,venue_image
              ,map_imag
           FROM
               seminars
          WHERE
               seminar_id = 
    $seminar_id";


  9. #9
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    For your purposes below is how I would write it.

    PHP Code:
    $seminar_id = (int) isset($_GET['seminar_id']) && is_numeric($_GET['seminar_id'])?$_GET['seminar_id']:0;

    if(
    $seminar_id) {
        require_once(
    $file);
        
        
    $sql 
        
    "SELECT
              seminar_name
              ,venue
              ,address1
              ,address2
              ,city
              ,state
              ,zipcode
              ,telephone
              ,listed_times
              ,venue_image
              ,map_imag
           FROM
               seminars
          WHERE
               seminar_id = 
    $seminar_id";

    Thanks for the code, but why is your code better than the way I have?

    (Just trying to become a PHP ninja some day!)



    Amy


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
  •