help preventing sql injection with pg_escape_string

[jake2891]

Hey guys im trying to work out how to prevent sql injection with the pg_escape_string function when the user input that is passed through the url via $_GET is an integer. Because even if i sanitize the data its still executing the injection. for example.

 

normal url http://test.com/?method_id=5

bad url http://test.com/?method_id=5 or 1=1

 

after i sanitze my $_GET data its still returning true and the sql injection is still working.

 

function sanitize_data($input_data) {
  
  $input_data = strip_tags(trim($input_data));
  
  return htmlentities(stripslashes($input_data), ENT_QUOTES);

}

function sanitize_sql($input_data){
  return pg_escape_string($input_data);
}


if($_GET['method_id']){
     
     $method_id = sanitize_data($_GET['method_id']);
     
     if($method_id){
        
            $method_id = sanitize_sql($method_id);
            
            $check_method_id = $test_db->queryOne("select method_id from tbl_methods where method_id = $method_id");
            
            if(!PEAR::isError($check_method_id)){
                
                if($check_method_id){
                   // should only get here if there is a valid method_id but with the 1=1 its always getting here?
                    echo 'here'; die();                   
                }else{
                    // log error   
                }
                
            }
                    
     }

}

[PFMaBiSmAd]

pg_escape_string, like its' name indicates is used for escaping string data (i.e. data that is enclosed in quotes in the query.)

 

For numeric data, you must validate that the data is just a number or simply cast it as a number in order to prevent sql injection.

[jake2891]

thanks makes sense.

[btherl]

Here's the modern way:

 

$check_method_id = $test_db->queryOne("select method_id from tbl_methods where method_id = E'$method_id'");

 

And the deprecated way:

 

$check_method_id = $test_db->queryOne("select method_id from tbl_methods where method_id = '$method_id'");

 

The only difference is the "E".  It tells postgres that an escaped string follows.  In practice it usually doesn't matter, but you might get a warning if you leave the "E" out.

 

Or as the other poster said, if you're dealing with a data type with limited values like an integer, then checking that it only contains digits is enough.  Then you don't need to use pg_escape_string for that value.