php pre prosessor - sanitizing parameters?

[paul vos]

Hi,

I'm using a php-processor-file. It works great, however I'm a bit concerned about the parameters I'm using in my query:

-----------------------------------

$param1 = $request->getParameter('param1');

if (isset($param1))  

    { 

        $where = "WHERE tbl.city = " . $param1  ;

    }

else 

    { 

        $where = 1;

    }

$query = $connection->fetchAll("SELECT * FROM mytable WHERE  $where ");

-----------------------------------

Should(n't) I be worried about sql injection?

Thanks & regards,

Paul

[Christoph Kappestein]

Hi,

yes please always use prepared statements if you put untrusted input into SQL. So yes this action would vulnerable to sql injections. Here an example with prepared statements which would be save:

$param1 = $request->getParameter('param1');

if (isset($param1)) { 

    $where = "WHERE tbl.city = :city";

    $params = ['city' => $param1];

} else { 

    $where = 1;

    $params = [];

}

$query = $connection->fetchAll("SELECT * FROM mytable WHERE  $where ", $params);

best regards

Christoph

[paul vos]

Great, thanks for the example Christoph! Best regards, Paul

Op maandag 7 december 2020 19:43:08 UTC+1 schreef Christoph Kappestein:

[Hisnan Faudan Azmia]

i tried like this : 

$param1 = $request->getParameter('param1');

if (isset($param1)) { 

    $where = "WHERE tbl.city = :city";

    $params = ['city' => $param1];

} else { 

    $where = 1;

    $params = [];

}

$query = $connection->fetchAll("SELECT * FROM mytable WHERE  $where ", $params);

but when i call the parameter query not working "An exception occurred while executing 'SELECT * FROM mytable WHERE WHERE tbl.city= ?' with params ["1"]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE tbl.city= ?'"

[Christoph Kappestein]

Hi,

so your query contains a duplicate "WHERE" and the condition contains also an alias "tbl" which is not available, this is probably the problem in your action.

best regards

Christoph

[Hisnan Faudan Azmia]

thanks i forgot about the table