SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    setting null in the db

    I want to set null in a table column if the input submitted by a user is an empty string.

    More specifically the user may enter or not the wwwaddress of his site-if he has one.

    Here is the code that checks if the user submitted an empty string or not and setting NULL to the
    db table if indeed he has done so.

    Code:
    function ajax_update_address($address,$city,$municipality,$wwwaddress)
    {
         global $conn; 
         $conn->set_charset("utf8");
         if($wwwaddress==' ')
         {$www=NULL;}
            
           $result = $conn->query('update busines_users
                set address="'.$address.'",city="'.$city.'",municipality="'.$municipality.'",wwwaddress="'.$www.'"
                where crID="12"');
             if (!$result){
             echo 'problem dude.';
             return false;
              }
              else
            {return true;} 
           
           
     }
    Unfortunately it does not work,in the db table in the wwwaddress column I still get an empty string.
    This conditional you see fails to do what it supposes to do.:
    Code:
      if($wwwaddress==' ')
         {$www=NULL;}

  2. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,446
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by designtrooper View Post
    This conditional you see fails to do what it supposes to do.:
    Code:
      if($wwwaddress==' ')
         {$www=NULL;}
    You've got a space between the quotes, so your check wouldn't actually match an empty string. You're better off using empty to check the value as this will catch both spaces, empty strings and other 'empty' values:
    PHP Code:
    if (empty($wwwaddress)) {
        
    $www NULL;


  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    You've got a space between the quotes, so your check wouldn't actually match an empty string. You're better off using empty to check the value as this will catch both spaces, empty strings and other 'empty' values:
    PHP Code:
    if (empty($wwwaddress)) {
        
    $www NULL;

    The basic logic of your code is OK-I tried this(for testing) and it worked:

    Code:
      if (empty($wwwaddress)) { 
        $wwwaddress= '5'; 
    }
    Nonetheless, $wwwaddress=NULL;, NULL does net get passed to the db table as 5 did above.

    I cannot understand why this is happening, the only thing I must add is that the column attributes are set by default to NULL.

    I do not know if this plays a role.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Please show us your current corrected code.

  5. #5
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    699
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    It's because you are trying to insert 'NULL' (with the quotes) and not null. As it stands right now your are wide open to injection attacks and such. You really should look at prepared queries. Or use one of the many libraries available.

    Just to clarify, your current code is generating:
    UPDATE business_users SET www = '' WHERE

    But basically you need to end up with: (note the lack of quotes around NULL)
    UPDATE business_users SET www = NULL WHERE ...

    And of course if www does have a value then you need (note the quotes around the address)
    UPDATE business_users SET www = 'sitepointebuilder.com' WHERE ...

    Might want to fire up the mysql console command and do a few updates manually until the difference is clear.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ι am posting the whole code...it is a function that includes the update statement:


    Code:
    function ajax_update_address($address,$city,$municipality,$wwwaddress)
    {
         global $conn; 
         $conn->set_charset("utf8");
       if (empty($wwwaddress)) { 
        $wwwaddress= NULL; 
    } 
     
    
           $result = $conn->query('update busines_users
                set address="'.$address.'",city="'.$city.'",municipality="'.$municipality.'",wwwaddress="'.$wwwaddress.'"
                where crID="12"');
             if (!$result){
             echo 'houston...we have a problem.';
             return false;
              }
              else
            {return true;} 
           
           
     }
    Observe the code ans tell me what you think.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Use this:

    if (empty($wwwaddress))
    $wwwaddress=null;
    else
    $wwwaddress="'".$wwwaddress."'";

    and change your query to :

    webaddress=$wwwaddress

  8. #8
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,446
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    As ahundiak pointed out, you should be using a prepared statement (or escaping, at the very least) for your DB query. Here's how you could rewrite your function (assuming that you're using the mysqli DB extension):

    PHP Code:
    function ajax_update_address($address$city$municipality$wwwaddress)
    {
        global 
    $conn
        
    $conn->set_charset("utf8");
        
        if (empty(
    $wwwaddress)) { 
            
    $wwwaddress NULL
        } 

        
    $stmt $con->prepare("UPDATE busines_users SET address=?, city=?, municipality=?, wwwaddress=?");
        
    $stmt->bind_param("ssss"$address$city$municipality$wwwaddress);
        
        if (!
    $stmt->execute()) {
            
    // Uncomment line below to troubleshoot query errors
            //echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            
    return FALSE;
        } else {
            return 
    TRUE;
        }
     } 
    Note that it's good practice to avoid using global variables in your code too. In this situation you could pass the $con object into the function as an argument.

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    As ahundiak pointed out, you should be using a prepared statement (or escaping, at the very least) for your DB query. Here's how you could rewrite your function (assuming that you're using the mysqli DB extension):


    Note that it's good practice to avoid using global variables in your code too. In this situation you could pass the $con object into the function as an argument.
    What is the reason for using a prepared statement?
    And regarding the thing about the global variables you are mentioning...you are right, will pass it as an argument instead.

  10. #10
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,446
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by designtrooper View Post
    What is the reason for using a prepared statement?
    Using prepared statements ensures that your data is properly escaped, which prevents SQL injection attacks (where an attacker tries to pass specially crafted SQL into your script, in an attempt to compromise your DB).

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    Using prepared statements ensures that your data is properly escaped, which prevents SQL injection attacks (where an attacker tries to pass specially crafted SQL into your script, in an attempt to compromise your DB).
    I usually do this with mysql_real_escape_string here is an example:

    Code:
     $wwwaddress=mysql_real_escape_string($_POST['wwwaddress']);
    Do you think I should ALSO use a prepared statement since I am using the above?

  12. #12
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,446
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    No, prepared statements also escape your data, so either use them or escape your data separately, but not both. You mention using mysql_real_escape_string so am I right in thinking you're using the mysql extension? If so, you should update your code to use the mysqli extension (or PDO), as the mysql functions are depreciated and are going to be removed from PHP.

  13. #13
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    699
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Notice also that with a prepared statement you don't have to worry about surrounding your data with quotes and that null values work as desired.

  14. #14
    SitePoint Enthusiast
    Join Date
    Jan 2013
    Location
    Greece
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    No, prepared statements also escape your data, so either use them or escape your data separately, but not both. You mention using mysql_real_escape_string so am I right in thinking you're using the mysql extension? If so, you should update your code to use the mysqli extension (or PDO), as the mysql functions are depreciated and are going to be removed from PHP.
    Well, you were right at pointing me that out because there is a mess here. Despite the fact the connection to the db is done with mySQLi:
    Code:
     $dbconnection = new mysqli('localhost', 'web', 'S52KmKXb5nF7WTUB', 'appointments');
    I use mysql_real_escape_string for escaping characters who of course is wrong and needs to be rewritten according to MySQli syntax


    Quote Originally Posted by ahundiak View Post
    Notice also that with a prepared statement you don't have to worry about surrounding your data with quotes and that null values work as desired.
    I will have that in mind.


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
  •