Suggestion for more secure PDO use

Hi all

In a system I have written, I was worried about potantial sql injection and found PDO prepared statements to be a fantastic way to avoid these problems.
I love a lot about opencart however I was disappointed to see it doesn't take advantage of prepared statements and have a solution which could be incorporated easily into the opencart core system.

In my system, to enable an easy conversion of my system, I took an existing PDO DB class (I don't remember where it came from) and wrote a function called pbexec which allows you to convert existing SQL quickly and easily as follows:

$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id)
WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "'
ORDER BY o.sort_order");

Simply replace all the variables with ?

$sql="...
WHERE po.product_id = ? AND od.language_id = ?
...";

Then call my routine:
$product_option_query = $this->db->pbexec($sql, "ii", $product_id, $this->config->get('config_language_id') );
(In 2nd parameter specify all the variable types (i=int,s=str etc..) followed by the variables in order... no need to worry about (int) or db escape)

It would need coding to fit inside your class but would be relatively easy.

Would anyone with control over the opencart core be interested in incorporating this feature?
I would be happy to help in the future but wouldn't have time in the next few months.

Best wishes
Laurence

Lozza2 wrote: ↑

Fri Feb 26, 2021 6:10 pm

Hi all

In a system I have written, I was worried about potantial sql injection and found PDO prepared statements to be a fantastic way to avoid these problems.
I love a lot about opencart however I was disappointed to see it doesn't take advantage of prepared statements and have a solution which could be incorporated easily into the opencart core system.

In my system, to enable an easy conversion of my system, I took an existing PDO DB class (I don't remember where it came from) and wrote a function called pbexec which allows you to convert existing SQL quickly and easily as follows:

$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id)
WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "'
ORDER BY o.sort_order");

Simply replace all the variables with ?

$sql="...
WHERE po.product_id = ? AND od.language_id = ?
...";

Then call my routine:
$product_option_query = $this->db->pbexec($sql, "ii", $product_id, $this->config->get('config_language_id') );
(In 2nd parameter specify all the variable types (i=int,s=str etc..) followed by the variables in order... no need to worry about (int) or db escape)

It would need coding to fit inside your class but would be relatively easy.

Would anyone with control over the opencart core be interested in incorporating this feature?
I would be happy to help in the future but wouldn't have time in the next few months.

Best wishes
Laurence

Huh ...

You could always create a new service request in the Commercial Support section of the forum to get this done as a custom job.

Huh ...

You could always create a new service request in the Commercial Support section of the forum to get this done as a custom job.

No offence but I don't think you understand the post!

You can make a pull request

https://github.com/opencart/opencart/pulls

Lozza2 wrote: ↑

Sun Feb 28, 2021 6:25 pm

Huh ...

You could always create a new service request in the Commercial Support section of the forum to get this done as a custom job.

No offence but I don't think you understand the post!

The offence's got nothing to do with the post as I did understood the post exactly as described. You are looking for custom codes that the core does not yet support by its constructor.