Is it possible to set parameter query of the URL?

Question

I want to know it it is possible for the developer to set the parameterised query of the url to mitigate SQL injection vulnerability?

For example:

https://example.com/somefile.php?id=1

How can a developer make a parameterised query for that? Like they do so in the parameter in the application?

score 0 · Accepted Answer · answered Oct 04 '17 at 05:05

Yes you can do it. Parameterized queries are simple and it force you to define the SQL query beforehand, and use placeholders for the user-provided variables within the query. You can then pass in each parameter to the query after the SQL statement is defined, allowing the database to be able to distinguish between the SQL command and data inputted by a user. If SQL commands are inputted by an attacker, the parameterized query would treat these as untrusted input, and the injected SQL commands will never get to execute. Watch out the example provided below for more understanding.

if (isset($_GET['id'])){
  $id = $_GET['id'];
  /**
   * Validate data before it enters the database. In this case, we need to check that
   * the value of the 'id' GET parameter is numeric
   */
   if ( is_numeric($id) == true){
    try{ 
      $dbh = new PDO('mysql:host=localhost;dbname=sql_injection_example', 'dbuser', 'dbpasswd');

      $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      /**
       * Before executing our SQL statement, we need to prepare it by 'binding' parameters.
       * We will bind our validated user input (in this case, it's the value of $id) to our
       * SQL statement before sending it to the database server.
       *
       * This fixes the SQL injection vulnerability.
       */
      $q = "SELECT username 
          FROM users
          WHERE id = :id";
      // Prepare the SQL query
      $sth = $dbh->prepare($q);
      // Bind parameters to statement variables
      $sth->bindParam(':id', $id);
      // Execute statement
      $sth->execute();

      $sth->setFetchMode(PDO::FETCH_ASSOC);
      // Fetch result
      $result = $sth->fetchColumn();
      /**
       * HTML encode our result using htmlentities() to prevent stored XSS and print the
       * result to the page
       */
      print( htmlentities($result) );

      $dbh = null;
    }
    catch(PDOException $e){
      /**
       * You can log PDO exceptions to PHP's system logger, using the Operating System's
       * system logging mechanism
       *
       * For more logging options visit http://php.net/manual/en/function.error-log.php
       */
      error_log('PDOException - ' . $e->getMessage(), 0);
      /**
       * Stop executing, return an 'Internal Server Error' HTTP status code (500),
       * and display an error
       */
      http_response_code(500);
      die('Error establishing connection with database');
    }
   } else{
    /**
     * If the value of the 'id' GET parameter is not numeric, stop executing, return
     * a 'Bad request' HTTP status code (400), and display an error
     */
    http_response_code(400);
    die('Error processing bad or malformed request');
   }
}
?>

basically it means in simple language, i.e. we just provide a variable to the id parameter and that variable have our query. So when the attacker tries to input it stored in somewhere but not effect the original query. So that our sql injection would not work?If i am wrong Please clarify me? — januu agrawal, Oct 04 '17 at 07:09
Oh thanx @SuvethanNantha your explanation really appreciated. — januu agrawal, Oct 04 '17 at 09:55

YoYo · Answer 2 · 2017-10-05T02:42:02.000

0

The issue is not in the URL, but how you use the parameters passed within the URL later on.

Best way is through the use of bind variables:

You can also ‘treat’ or 'sanitize' your parameters by escaping and/or removal of suspicious data. This is usually a very tricky thing to implement susceptible for mistakes. Hence bind variables is just simpler and safer to use.

edited Oct 05 '17 at 02:42

answered Oct 04 '17 at 05:25

YoYo

9,157
8
57
74

Thanks @YoYo but its too confusing. But i will try to understand it. – januu agrawal Oct 04 '17 at 07:14
hello, can you please check it, i posted an comment below which shows what i understand about it. – januu agrawal Oct 04 '17 at 08:55
You explained it well back. Additionally, the accepted answer has a nice example of how bind variables can be used - but is really duplication of other posts. Most notable this one - https://stackoverflow.com/a/60496/744133 – YoYo Oct 05 '17 at 02:32

Is it possible to set parameter query of the URL?

2 Answers2