Secure dynamic query (PDO)

General discussions related to php

Moderators: egami, macek, gesf

Post Reply
User avatar
dinoroger
New php-forum User
New php-forum User
Posts: 12
Joined: Mon Feb 20, 2017 1:59 pm

Tue Feb 21, 2017 7:47 am

Yes I know the title for many is an oxymoron. ;) I am attempting to create a database connection and query management system that auto switches the connections and dynamics of the query based on the web server that the code is running from. The function will ask for a connection id, query id and array of dynamics and figure everything else out based on the settings you configured (this web server = this connection...) Also the function is both a PHP function and a JavaScript function using AJAX to do the PHP call for you and return back to the client. For example you have a development, qa, training, and, production server you can create one query and it will work for all web servers without needing to change any code. In the query for example the only difference between the queries would be the database and table name.

DEV = SELECT * FROM mydevdb.table
QA = SELECT * FROM myqadb.table
TRAINING = SELECT * FROM mytrainingdb.table
PRODUCTION = SELECT * FROM myproductiondb.table

The above is just a simple example and there are many more uses for needing a truly dynamic query. Some of the apps I write have hundreds of queries and plenty of switch statements that could be eliminated with such a system.

Now for the WHERE value dynamics I will be still using prepared statements so that is fairly secure. The problem is with all the other areas of the query that I would like to make dynamic where prepared binded params will not work. In the example above the database and table name.

So my thought would be to just filter the _POST values that come in. Since most of these other dynamics are dealing with database name, table name, select columns…

Here are the characters I plan on filtering and wanted your input on other characters and also other things I could do to add a little more protection other than just not do it at all. ;)

:;,%*=+/\|#-<>^'" and the word UNION

I am just getting started with this so I have no code to show currently. I am just getting my thoughts collected on how either this is a bad idea or if there is a glimmer of hope to do this and be safe at the same time. Thanks
Last edited by dinoroger on Tue Feb 21, 2017 8:52 am, edited 1 time in total.
NigelRen
php-forum GURU
php-forum GURU
Posts: 622
Joined: Fri Aug 05, 2011 9:53 am

Tue Feb 21, 2017 8:26 am

I'm not sure about the idea of having different stages of development having the same set of code files - except for the database, surely the idea of having different servers is so that the changes can be kept separate and there is no chance of side effects of changing one of them. Even if it's not a different physical server, having a separate set of code directories is essential I would have thought.

As for which DB to connect to - why not just have a DBName value set in a header file on each specific server...

Code: Select all

$dbName='mytrainingdb';
Then your queries become...

Code: Select all

SELECT * FROM $dbName.table
As for
The problem is with all the other areas of the query that I would like to make dynamic where prepared binded params will not work. In the example above the database and table name.
If you can give an example of where you think this is a problem, then perhaps it would be easier. If your planning on doing all of your database access through this mechanism, then I would suggest this is not a good idea. The performance will be terrible and the chance of something going wrong is very high.
User avatar
dinoroger
New php-forum User
New php-forum User
Posts: 12
Joined: Mon Feb 20, 2017 1:59 pm

Tue Feb 21, 2017 8:51 am

So let me give you more details on why I want such as system and maybe it will make more sense:

Between all our servers other then when developing we want to keep all code on all servers the exact same. We even have pages that detect if a server file is different via modified date or size between the servers. This allows us to easily see what files need to be updated when code gets green lighted. Yes I know there are code management systems that can do all that fun stuff. Using pre-filled variables and switch statements is a valid method I am looking to create a simple user interface to manage all the connection queries and dynamics instead.

The main reason for this is we have 20 people in the office that know MySQL and SQL very well but do not know PHP or may not have access to the PHP code (web server) but are still completely trusted to deal with the databases. With the method I am trying to create, I can give them full control over the database side of a web app without them ever needing to write any code or have access to the PHP files. The security of getting to this admin interface is another topic I will discuss later but I have some ideas.

The plan is to create a truly simple system that can be plugged into any web app and give these features. I am very capable of writing code that can write it's own code to automate many processes that are required to do this. I feel it is a big undertaking but has merit. I do not mind spending the time to try and make it bullet proof as it would need to be. I understand that right now the formula in everyone thought process is RISK > REWARD but I would like to change that to just REWARD. ;)
User avatar
Strider64
php-forum GURU
php-forum GURU
Posts: 317
Joined: Sat Mar 23, 2013 8:24 am
Location: Livonia, MI
Contact:

Tue Feb 21, 2017 11:44 am

Maybe something like the following:

Code: Select all

if (filter_input(INPUT_SERVER, 'SERVER_NAME', FILTER_SANITIZE_URL) == $thisServerName) {
	define('DATABASE_TABLE', '$thisServerTable);
}
Obviously with switch instead of an if statement otherwise you'll have a lots of if statements. This isn't full proof then again nothing is and I think a code management system would be the better solution. Especially if there are people who know MySQL and SQL, but do not know PHP or have access to it.
User avatar
dinoroger
New php-forum User
New php-forum User
Posts: 12
Joined: Mon Feb 20, 2017 1:59 pm

Tue Feb 21, 2017 12:12 pm

Thanks but the example I gave is not the problem as it was just an example. It could be the database name, table name, select column name, order, group, and any number of things that could be dynamic. So the management system does not care what you want to be dynamic as long as it follows some rules. These rules again:
  • If the dynamic is part of the WHERE value statement of the query the dynamic word would follow the typical prepared binding param rules where you use a colon and the dynamic word. Example WHERE age > :age. When the query is built it will detect that your dynamic word to replace started with a colon (:) and then do a bindParam of the value.
  • If any other dynamic then the dynamic word and value must not contain the word UNION or any of the characters :;,%*=+/\|#-<>^'". If a character is found the query call will die and return nothing.
So following this character rule it should not affect columns, database names, table names, group, and order by and many other parts of a query other that math, comparisons, and maybe a few more. So the dynamics would have a limit on what it can and cannot replace. All of this filtering of course is done on the PHP code side so a hacker could inject what ever they wanted and it would detect it before it even got to the query.

Here is a basic crude mockup of such a function:

Code: Select all

function checkForBadDynamics($str){
	$str = rawurldecode($str);
	if (strpos($str, ':') !== false){return false;} 
	if (strpos($str, ';') !== false){return false;} 
	if (strpos($str, ',') !== false){return false;} 
	if (strpos($str, '%') !== false){return false;} 
	if (strpos($str, '*') !== false){return false;} 
	if (strpos($str, '=') !== false){return false;} 
	if (strpos($str, '+') !== false){return false;} 
	if (strpos($str, '/') !== false){return false;} 
	if (strpos($str, '\\') !== false){return false;} 
	if (strpos($str, '|') !== false){return false;} 
	if (strpos($str, '#') !== false){return false;} 
	if (strpos($str, '-') !== false){return false;} 
	if (strpos($str, '<') !== false){return false;} 
	if (strpos($str, '>') !== false){return false;} 
	if (strpos($str, '^') !== false){return false;} 
	if (strpos($str, "'") !== false){return false;} 
	if (strpos($str, '"') !== false){return false;} 
	if (stripos($str, "UNION") !== false){return false;} 
	return true;
}
NigelRen
php-forum GURU
php-forum GURU
Posts: 622
Joined: Fri Aug 05, 2011 9:53 am

Tue Feb 21, 2017 12:58 pm

My concern is that the scope will creep to the point where it becomes unmanageable....
  • Sub selects - which is a select which can appear in a variety of places (columns, where, group by and having clauses) are a very useful piece of SQL which could cause a lot of confusion.
    Inserts using selects to populate data.
    How do you use unions when you want to exclude this being part of the parameters.
You've already identified times where some characters aren't allowed - and sometime are - how are you going to know when it's valid.
Your choice could to be to cut down real SQL to a more manageable subset - If you can then it would make the target area a more secure option. Still not sure about performance though.
User avatar
dinoroger
New php-forum User
New php-forum User
Posts: 12
Joined: Mon Feb 20, 2017 1:59 pm

Tue Feb 21, 2017 1:26 pm

You can still use UNION in the query you just can't make a dynamic word with the word UNION or the value you are replacing it with contain the word UNION. Most of the times the query will be 99% intact as a normal query with only a few places being dynamic. I just did not want to limit on what parts can be dynamic. If someone wanted to make 50% of their query dynamic I agree that is a little overboard and more thought process should go into the query or a group of queries with a little less dynamics applied.

Some examples of dynamics (this is not a limited set just a handful of some situations):

WHERE age > :age
You would send it :age and the value. $dynamics[":age"] = 21;
The system would detect the : and use the prepared bind param method as noraml.

FROM [mydatabase].[mytable]
You would send it "[mydatabase]" and a value. You would also send it "[mytable]" and a value. $dynamics["[mydatabase]"] = ""dbname"; $dynamics["[mytable]"] = ""tbname";
The system would detect it as not a prepared bind param and then check the value for an invalid character. If an invalid character is found it would stop processing the query and do some stuff like log to the console or send back and empty array...

SELECT name, age, [TESTCOLUMN]
You would send it "[TESTCOLUMN]" and a value. $dynamics["[TESTCOLUMN]"] = "mynewcolumnname"
The system would detect it as not a prepared bind param and then check the value for an invalid character. If an invalid character is found it would stop processing the query and do some stuff like log to the console or send back and empty array...

An example of 2 of the processes to cycle through the 2 different dynamics:

Code: Select all

foreach ($dynamics as $key => $value) {
	if (strpos($key, ':') === false){
		//Non bind param
		if (checkForBadDynamics($key) == false || dg_CheckForBadDynamics($value) == false){
			logToConsole("PDG: Invalid dynamic = ".$key." -> ".$value.". Special characters are not allowed. See documentation.");
			return $returnArray;
		}
		$query = str_replace($key, $value, $query, $count);
	} else {
		//Prepared binded param
		$val = $value; //It is weird it will not let you use $value from the loop and this is the method I found that works
		$comm -> bindParam($key, $val);
	}
}
User avatar
dinoroger
New php-forum User
New php-forum User
Posts: 12
Joined: Mon Feb 20, 2017 1:59 pm

Thu Feb 23, 2017 10:24 am

I am getting close to having a prototype finished of a complete interface system and was wondering if any would volunteer to take a look at it when ready. It would be a little too large just to paste the code on the forum but I have a website where I can place the documentation, videos, and download. This would be a beta if not alpha version but so far it is working fairly well. I hope to maybe be finished by the end of the weekend on 2/26/17. Just post a comment here or private message me if interested and it would be greatly appreciated. In the video I will create I will try to address more when such as system would make more sense. Thanks
Post Reply