PDO select query with unique LIMIT. Secure?

[Cobra23]

I have searched for a solution to a code that I wrote, with unique numbers in the SQL statement of LIMIT at the end and using execute().  I have also set

PDO::ATTR_EMULATE_PREPARES, false

I know that there is ways to use bindValue() or bindParam() instead of execute() for this. However, the way I have it set up works, but is there a security flaw with the way I am using LIMIT and should I be using bindParam() instead of execute()?

<?php
if ($numbered_row == 0) {
	$limitation = '';
} else {
	$started_page = ($page_number - 1) * $items_on_each_page;
	$limitation = ' LIMIT '. $started_page . ',' . $items_on_each_page;
}
$sql_query = "SELECT * FROM `myTable` WHERE `id` = :id AND `group` = :group AND `name` LIKE :name AND `country` = :country ORDER BY `date` DESC" . $limitation;
$query_result = $pdo_connecting->prepare($sql_query);
$query_result->execute(array(':id' => '73', ':group' => 'Furniture', ':name' => '%'.$name.'%', ':country' => $country));
?>

 

Edited November 21, 2018 by Cobra23

[requinix]

Your LIMIT depends on $started_page and $items_on_each_page. Can you explain to me exactly where those values are coming from? What values they might possibly ever have? And can you describe how SQL injection works?

I'm asking because you already know the answer, you just don't know that you know.

[Cobra23]

I wasn't expecting this reply. But I will answer them anyways, the $items_on_each_page is the default number I set (eg. 50) as of how many items are allowed on each page. Before we get the $started_page, we need the $page_number which is coming from the $_GET variable in the browsers link which will either be 1 or greater. Never 0 or a non number.

As for SQL injections, thats the user injecting malicious code into the user inputs which in turn can exploit the database. But with PDO done right, filtering, sanitising and validating user input, the system can be safe from SQL injection.

From what I know and how you went about your reply, it seems like the LIMIT is safe to use the way I have it. I just wasn't sure if thats the case with PDO as the LIMIT was outside the query.

Thanks requinix

[requinix]

I like doing that kind of reply because then it leads into my reply below:

1 hour ago, Cobra23 said:

the $items_on_each_page is the default number I set (eg. 50) as of how many items are allowed on each page.

In other words, you have full control over that value and the user cannot influence it. You're not going to SQL-inject yourself, right? So it's safe.

1 hour ago, Cobra23 said:

Before we get the $started_page, we need the $page_number which is coming from the $_GET variable in the browsers link which will either be 1 or greater. Never 0 or a non number.

True, $page_number comes from the user, but that's not the value you're putting into the query. Literally it's $started_page. And $started_page is the result of some math.
Math only ever creates numbers. It alone cannot cause SQL injection, which needs things like keywords and symbols and those can only appear in strings. Therefore $started_page is also safe.

I could have said simply "yes, it's safe" but that doesn't explain why it is. This way does, and remembering it means that next time you'll know the answer.