PDO Statement Function

Question

I use prepared PDO statements, working on my current project, I decided to create several functions for each action that I need. Below is an example of extraction

function db_fetchAll($sql, $param) { global $db; $stmt = $db->prepare($sql); if (empty($param)) { $stmt->execute(); } else { $stmt->execute($param); } $count = $stmt->rowCount(); if ($count == 0) { $result = ""; } elseif ($count == 1) { $result[] = $stmt->fetch(); } elseif ($count > 1) { $result = $stmt->fetchAll(); } return $result; } 

Example

 $database = db_fetchAll("SELECT * FROM database_table WHERE id=:id", array(':id' => $id)); 

It only condenses query lines from 3 lines to 1, but with the amount of information needed for each page, it needs to be minimized.

I return to the project in one last pass, and I just wanted a second opinion on the security of this. If there is something that I should add, etc. All user_input are passed through this function.

 function user_input($input) { $input = trim($input); $output = strip_tags($input); return $output; } 

and all output uses htmlspecialchars.

So, the question in a nutshell: Is it safe? Is there anything else I could do to prevent other forms of injections, etc.

I fully understand how prepared statements work, I’m just more thorough, version 1 of this site was a nightmare, tons of injections, access to administrator accounts, etc.

Answer 1

+1 for that intention. This is a rather awkward number, but only one out of thousands of users asking questions regarding PDOs ever thinks of such a natural thing as a helper function.

Just a few notes.

• First of all, there is a significant flaw in your code that tries to automatically determine the type of result. I tried it myself, and I can assure you that this is just a source of disaster. The less magic in your code, the more hair on your head. Thus, instead of such unreliable magic, just create different functions to return different sets of results.
• Secondly, should you make a default value for the parameters? to be able to run the request without placeholders.
• Thirdly, some code is just superfluous. There is no need to check the $ param variable.

Finally, your function should look like

 function db_fetchAll($sql, $param = array()) { global $db; $stmt = $db->prepare($sql); $stmt->execute($param); return $stmt->fetchAll(); } 

and always return an array of strings, as the name suggests

Like other functions with different result sets, it seems that PDO, if slightly modified, can offer exactly the same convenience without any auxiliary function at all. You can take a look at the PDO wrapper I made to ease the pain of switching from old mysql ext

Your code will remain almost the same

 $data = DB::query("SELECT * FROM database_table")->fetchAll(); 

but it will allow you to use all possible PDO variants of different methods:

 $row = DB::prepare("SELECT * FROM table WHERE id=?")->execute([$id])->fetch(); 

or even

 $sql = "SELECT name FROM table WHERE id=?"; $name = DB::prepare($sql)->execute([$id])->fetchColumn(); 

etc.