Query about ease of use in PHP and why it is not possible to do what I describe

Question

[not a duplicate - see explanation on bottom of question]

I have a query about something I've been thinking about - starting to write PHP code for a large site and why it is not possible to simplify your PHP code to the max.

Here is what I mean: The site I'm working on has a functions.php for (you guessed it) all their PHP-function - the login-function + all the getTable-/insertTable-/updateTable-functions etc.

So it has a getAllVideos-function for the page that shows all the videos on the page... and a getVideo-function for the Video View page where you can play the selected video... and a getVideoRecommended-function for the homepage where at the top you can view the recommended videos selected and ordered by a recommended algorithm... and an updateAvatar-function for users to update their avatar picture... and a... you get the picture...

All in all, with all the login-/video-/userinfo-/get-/insert-/update-/etc-functions the functions.php file is 21.347 lines long and sometimes a hassle to troubleshoot.

So for about 10 sec, I thought about and found, what I thought was a VERY reasonable solution to truly simplify the code, and make it about 2-300 lines long and VERY easy to troubleshoot... then I tried it, and was sorely disappointed that it doesn't work, and don't understand why.

Solution: As an example first we write an all-inclusive get-function with the needed variables to be passed and set their default values to ALL where possible

<?php
    function getTable($pdo,$columnNames = "*",$tableNames,$condition = 1,$orderBy = "date",$limitOffset = 0,$limitMax = 65000) {
    // Reset variables
    // Reset session cookies

Then we do the query using the values in the variables where possible

    $prepareTable = $pdo->prepare( "SELECT ? FROM ? WHERE ? ORDER BY ? LIMIT ?, ?" );
    $prepareTable->execute([$columnNames,$tableNames,$condition,$orderBy,$limitoffset,$limitmax]);
    $getTable = $prepareTable->fetchAll();

Then we check if anything found and do what we need to do

    if (!getTable) {
        // Take appropriate action, or write appropriate error message
    } else {
        // make a variable for each column by exploding $columnNames by , and create a session cookie for each to use on webpage
        $columnName[] = explode(',',$columnNames);
        foreach ($columnName as $row) {
            $_SESSION['variable'][] = $row['$columnName'];
        }
    }
?>

And that's it... you now have one single select query that matches ALL your needs

Now let's say on the video page I want to show all the video thumbnails + titles + descriptions I just call all videos by first defining the necessary variables

<?php
    $columNames = "thumbnail,title,description";
    $tableNames = "videos";

And then just call the getTable-function (which would be the same for ALL queries)

    getTable($pdo, $columnNames, $tableNames, $condition, $orderBy, $limitOffset, $limitMax)
?>

And it will write a select statement that look like

SELECT thumbnail,title,description FROM videos WHERE 1 ORDERBY date LIMIT 0, 65000

and search and find thumbnail, title and description from all the videos on the site

or let's say I wanted them ordered by title instead, I'd just add

    $orderBy = title;

etc.

and if I wanted only 5 results I would just add

    $limitMax = 5;

etc.

You get the picture.

Using this approach you could create any possible SELECT-statement you wanted using only one getTable-function (and the same would apply with only one INSERT-statement, only one, UPDATE-statement, etc.), reducing the function.php from over 20.000 lines to less than 200 no matter the size of your site.

Plus when it comes to troubleshooting:

Troubleshooting now:
There is a problem with the channel-page...
1. go to channel-page and see what function is used
2. go to functions.php and scroll through 20.000+ lines to find the function used
3. find, fix and check error
4. (if more than one function used) repeat 2, 3 and 4 as many times as necessary

Troubleshooting with my approach:
1. go to channel-page and find, fix, and check error in 2-20 lines of code where the getTable-function is called
And Done!

So not only would my approach GREATLY decrease the size of functions.php to max 300 lines of code no matter the size of your site and database... but once you've checked that it works on one page you now NEVER have to go back and check functions.php for errors again... ALL errors from then on will ALWAYS be found right on the specific page where the problem is... and ALL troubleshooting will be done on max 9 lines of code pr. function-call checking the variables you defined... and that's it...

My query to the fine people on this site is why has PHP made it impossible for my solution to work? (you can't use variables for colomNames, TableNames, entire condition, and/or orderBy - you still need to specify the columnNames, tableNames and columnName in the condition and orderBy in separate functions as needed

SELECT title (columnNames)
FROM videos (tableNames)
WHERE username = ? (condition)
ORDER BY date (orderBy)

etc., which makes it necessary to make a separate getTable-function for each need you have which defeat the purpose of my solution and keeps functions.php growing as your need, site and database grows)

How is my solution not a possibility?

Somebody please tell me why passing variables to columnNames, tableNames, entire condition and orderBy is a security risk more so than the other values (at least to a point where it shouldn't be possible to do what I described above)

edit: removed the maybe dublicate but insignificant second question since it is... well insignificant to the main question I seek answers to

Thank you in advance

APM

Difference:

I only ask IF/HOW it's possible as an after thought (because there might have been a solution I haven't thought about - which there seem to have been - see accepted solution)

I know it is considered a security risk because it defeats the security of prepared statements... and I know it is not possible to do exactly what I want (have been thrown enough Fatal Errors to know this)... my main question is not how or why in the sense that I don't understand why the code don't work... my main question is why is it not possible to do what I describe?:

They can recommend the right use as often as they like - but on a SELECT-/UPDATE-/INSERT-query where security is not an issue - like on showing all videos or showing some videos or... why have they made it impossible to do what I propose?

as it stands now - to do what I want (reduce code significantly and even more important make troubleshooting a breeze) I will have to use accepted solution which is a little more cumbersome than what I wanted to do (but still much better than what is being done now) or use two connection to the database - one PDO (for instance the login function where security is paramount) and one 'old-school' mysqli-query for the security-not-needed functions...

and that just bring on a whole new set of issues

that's why my question was different... my question is more a philosophical one of why I don't have the choice to take what security measures in want and skip security when I want since it is my site to do with as I please (in theory)... is the security risk really that great that I shouldn't be allowed to do this? and if so why? - that was my main question...

So Shadow... please remove your duplicate claim or point to where my main question have been asked and answered (which incidentally isn't covered by the one you linked to)

Answer 1

Your question appears to come down to wanting to use placeholders for the parts of an SQL statement, but for any part, and not just the actual parameters that they are designed for. You can build them with variable interpolation ("SELECT {$fields} FROM {$table} WHERE {$whereClause} ORDER BY {$orderField} {$orderDirection}"), but that may also do away with some of the security that prepared statements (with the data in placeholders) gives.

The strings like 'whereClause' could still use the placeholders, but they would have to be passed in and match-up (named placeholders would make this a lot easier).

$whereClause = "(tablename.username = :username) OR (tablename.userid = :userid)";
$orderField = 'tablename.username';
$orderDirection = 'DESC';
$parameters = ['username' => 'fred', 'userid' => 5];

A better long-term solution is to split the queries into various classes that can re-use and extend from a common base where the SQL-building occurs. Here, the queries would be in their own classes, probably named after the database-tablename. You could, for example, have the (abstract) base-class require data (the basic SQL query, with the fields, where-clauses and ORDERing) being fetched from the sub-classes methods. Putting the methods into classes should also enable better testing - even better if you write the tests first (writing what youi expect to be able to happen), and then write the code to allow the tests to run successfully.

You can then also create more specialised functions in the classes for more involved queries.

Even better still is to use a common library that uses a design pattern like 'Object Relationship Mapper' (ORM), or 'Active Record'.

Answer 2

This is not a security problem, but rather about what you are doing.

prepare prepares the query on the database ('compiling', optimization etc.) and then just waits for the variables. Obviously it needs all SELECTS etc. beforehand for that.

You can however build a string like "SELECT $a FROM $b WHERE $c = ?" etc. and it should work.