Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts

    preventing SQL injection

    Hey guys so i have a form where i am getting data from text fields that the user will enter his/her data into and then passing those data fields into variables and then passing them in a SQL query to be saved into the database being used.

    I am trying to figure out how to use mysql_real_escape_string on any text input field to prevent sql injection and causing problems like when a user enters a last name like: O'leary the data is being saved like this: O\'leary i have tried to add the mysql_real_escape_string in my query where i am passing the variables that contain all the data from the text fields but so far it hasn't worked for me.

    Can someone please tell me the correct way to use this function sot that way the data will be validated. I AM DOING THE mysql_real_escape_string in my SQL QUERY

    here is my code that gets whatever the user enters from the text fields and then assigns them into variables. the sql INSERT statement can be found at the bottom of this code snipet:
    Code:
    <?php
    // a class
    class User
    {
       public $user, $pass, $fname, $lname, $role, $role2;
       public $user_err, $pass_err, $name_err, $pass_err2, $pass_err3;
    
       public function __construct( $post_array ) {
          $this->user = $_POST['username'];
          $this->pass = $_POST['password'];
          $this->pass2 = $_POST['password2'];
          $this->fname = $_POST['first_name'];
          $this->lname = $_POST['last_name'];
    	$this->role = $_POST['role'];
    	
    
          $this->user_err = NULL;
          $this->pass_err = NULL;
    	  $this->pass_err2 = NULL;
    	  $this->pass_err3 = NULL;
          $this->name_err = NULL;
       }
    
       public function validate() {
    
          // username isn't a duplicate
          if( !$this->user ) {
             $this->user_err = "Please specify username";
          } else if( duplicate( $this->user ) ) {
             $this->user_err = "That username is already in use";
          }
    	  
          // passwords match and are at least 6 chars
          if( !$this->pass || strlen( $this->pass ) < 6 ) {
             $this->pass_err = "Password must be at least 6 characters";
          } else if( $this->pass != $this->pass2 ) {
             $this->pass_err = "Passwords do not match";
          }
    	  
    	  //user forgot to provide a password
    	  if( !$this->pass || !$this->pass2) 
    	  {
    		$this->pass_err2 = "Please provide a password";
    	  }
    
    	  //user forgot to provide a ROLE (doctor or nurse)
    	  if( $this->role == 0)
    	  {
    		$this->pass_err3 = "Please select one Role";
    	  }
    	  
          // first/last name aren't blank
          if( !$this->fname || !$this->lname ) {
             $this->name_err = "Please provide a first and last name";
          }
    
          return !$this->has_errors();
       }
    
       public function has_errors() {
          return $this->user_err || $this->pass_err || $this->pass_err2 || $this->pass_err3 || $this->name_err;
       }
    
       public function insert()
       {
       
    $sql = "
    INSERT INTO users 
    (username, f_name, l_name, role, pword)
    VALUES ( '$this->user', mysql_real_escape_string('$this->fname'),  mysql_real_escape_string('$this->lname'), '$this->role', aes_encrypt( 'The Secret Phrase', '$this->pass' ));";
    
    //echo "<hr>DEBUG SQL: " . $sql . "<hr/>\n";
          mysql_query( $sql ) or die( "Error( $sql): " . mysql_error() );  
    
       }
    }
    
    function duplicate( $username )
    {
    	//function used to test for duplicate usernames 
       $sql = "SELECT user_id FROM users WHERE username = '$username'";
    
       $result = mysql_query( $sql ) or die( "Error( $sql): " . mysql_error() );
    
       return mysql_num_rows( $result ) > 0;
    }
    
    ?>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I think you need to learn more about PHP.

    You can't invoke a *FUNCTION* in the middle of a string literal.

    Code:
    $sql = "INSERT INTO users "
         . " (username, f_name, l_name, role, pword) "
         . " VALUES ( '$this->user', '" . mysql_real_escape_string('$this->fname') . "',"   
         . "'" . mysql_real_escape_string('$this->lname') . "','$this->role', "
         . "aes_encrypt( 'The Secret Phrase', '$this->pass' ));";
    Though why you wouldn't want to escape $this->user and $this->role mystifies me.

    If they are numbers, then why do you have apostrophes around them?

    And if they aren't numbers then why aren't they also escaped?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    So i copied and used the sql query you wrote and i did a debug to see if it would work and it didn't it seems that it is either missing a quote or has one to many also it is showing that it still is not fixing the issue if the users last name has an apostrophe ??? It is actually passing the string:

    DEBUG SQL: INSERT INTO users (username, f_name, l_name, role, pword) VALUES ( 'richard1', . mysql_real_escape_string('Richard') . , .mysql_real_escape_string('O\'King') . , '1',

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    This makes no sense.

    Where did the \' in O'King come from if not from using mysql_real_escape_string???

    And *YOUR* code would have put the mysql_real_escape_string in there in the SQL, but mine shouldn't, since non of them are between "..." marks!

    And where did the apostrophes go that I put in there. For example, where did the one from "'," go???

    Or for that matter, where is the one that is just "'" ????

    Are you sure you are running my code and not what you had originally??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    i copied exactly what you gave me and used it and when i check what is being saved into the DB for fname and lname it is always:

    fname: $this->fname
    lname: $this->lname

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Yes because you are including quotes where they don't belong:
    mysql_real_escape_string('$this->fname')
    so it is converting what was a variable into a string so that string is what is being inserted and not the value in the variable.

    remove the single quotes and use only:
    mysql_real_escape_string($this->fname)

    still better though is to remove the MRES entirely from your query and use it earlier in your script:

    $this->user = mysql_real_escape_string($_POST['username']);

    You should also look up the MYSQL_PDO extensions and learn how to use them instead.

  • #7
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    guelphdad:
    okay so i tried what you said and in my function where i make a variable to store the data the user enters i did this:

    $this->fname = mysql_real_escape_string($_POST['first_name']);
    $this->lname = mysql_real_escape_string($_POST['last_name']);

    then in the function where i actually do the sql query i kept the '$this->fname', '$this->lname' but it still does not correct the problem of a user with the last name like O'King and saving it in the DB like this: O\'King i also tried removing the single quotes but it just gave me an error so i still dont know how to solve this problem i am having

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    YOU WANT IT TO SAVE IT AS O\'King !!!!!

    MySQL recognizes \' as an *embedded* apostrophe!

    If you had looked in the table after inserting O\'King, you would see that what is actually stored there *IS* O'King.

    TRY IT.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    YOU WANT IT TO SAVE IT AS O\'King !!!!!

    MySQL recognizes \' as an *embedded* apostrophe!

    If you had looked in the table after inserting O\'King, you would see that what is actually stored there *IS* O'King.

    TRY IT.
    Old Pedant:
    Sorry, but when i check my table in my DB it DOES NOT show as O'King and if i try to echo it out on the actual web page it displays like O\'King, incorrectly. So im sorry if maybe i am misunderstanding the way mysql_real_string works but i thought that it would be used to take care of problems like that

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I don't know what to tell you.

    Look at this copy/paste from my session with MySQL:
    Code:
    mysql> create table demo( name varchar(30) );
    Query OK, 0 rows affected (0.23 sec)
    
    mysql> insert into demo (name) values('My name is O\'Brien');
    Query OK, 1 row affected (0.18 sec)
    
    mysql> insert into demo (name) values('I don\'t like it without the \'!');
    Query OK, 1 row affected (0.03 sec)
    
    mysql> select * from demo;
    +--------------------------------+
    | name                           |
    +--------------------------------+
    | My name is O'Brien             |
    | I don't like it without the '! |
    +--------------------------------+
    2 rows in set (0.00 sec)
    So whether it is because of your PHP class or what, I don't know. But MySQL *clearly* correctly treats \' as an embedded '

    And, yes, mysql_real_escape_string *should* convert ' to \'
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Were I guessing, I'd guess that somehow you are calling mysql_real_escape_string *TWICE* on the same value. And then, indeed, you would get those results.

    Because then ' would first become \' and then the second call to the function would convert the \' to \\\'

    Look what happens to that with MySQL:
    Code:
    mysql> insert into demo (name) values('this is what \\\' becomes');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from demo;
    +--------------------------------+
    | name                           |
    +--------------------------------+
    | My name is O'Brien             |
    | I don't like it without the '! |
    | this is what \' becomes        |
    +--------------------------------+
    3 rows in set (0.00 sec)
    Maybe your PHP class is *already* calling mysql_real_escape_string for you and you didn't know it?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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