Ok I haven't heard much feedback yet so I guess that is somewhat good?
I am currently working on some "proposed" new methods to work with this new Database connector. The methods as exist should remain working for the time being but if this connector is accepted there won't be a need for a lot of the old stuff.
Currently if you access the database connector directly when you do SELECT, INSERT, DELETE and UPDATE queries you use the query statement. This would look similar to this:
$sql = 'SELECT * FROM table ORDER BY T_id';
$result = $testDB->query($sql);
An insert would be done like:
$sql = 'INSERT INTO table (LastName, FirstName) VALUES ('McBeth', 'Lady' )";
$result = $testDB->query($sql);
And so on.
We are using the same code along the way and it is relatively simple to inject code into this if you don't use some sort of sanitizer. Xoops has all sorts of sanitizers throughout the code but on occasion something slips through. Not to mention the times you might not quite have your code written 100% right.
I am looking at building methods that would allow the same functionality we have now but will make calling the queries simpler, allow us to make use of features of the different databases transparently and should be a lot more secure since we would be using prepared statements.
The idea is that instead of sending the actual sql query to our code we want to send JSON style data to the methods and it will go from there. Why JSON? JSON is a common structure used throughout web design and works well for us here. I am using the "PDO 4 you" class for a basis for most of this code although it is modified anywhere it is used.
A simple example would be to look at a normal delete query.
Normally we might call a delete similar to the ones above.
$sql = 'DELETE FROM TABLE WHERE FirstName='Mickey' AND LastName='Mouse'";
$result = $testDB->query($sql);
This is simple if we only want to delete one entry but if we wanted to delete a series of records we would have to repeat our setup of the query then call it over and over again. And check it each time to make sure it worked.
With this "new" method we call the same routine differently.
$sql = '
{
query : [
{
table: "TABLE" ,
whereAnd: { FirstName: 'Mickey', LastName: 'Mouse' }
}
]
}
';
$result=$testDB->delete($sql);
This may seem a bit of work for something simple but if you want to do more than one deletion you could do something like this:
$sql = '
{
query : [
{
table: "users" ,
where: { id: 2 }
},{
table: "users" ,
where: { id: 5 }
},{
table: "users" ,
where: { id: 10 }
},{
table: "books" ,
where: { id: 10 }
}
]
}
';
$result=$testDB->delete($sql);
This would delete 4 separate records in two different databases securely and simply.
This is what I am working on now...
It really makes a difference for select queries as well.
(Note you need to count your own rows with PDO. There is no rowCount
outside of MySQL normally and PDO programmers talk about it is better to
count your own rows even with MySQL and MySQLi connectors because it
is not accurate in ALL cases.)
So currently you might use the following code to read in data and get the number of rows:
$sql = 'SELECT * FROM table ORDER BY T_id';
$result = $testDB->query($sql);
if (!error())
$rowCount=0;
while ($row = $testDB->fetchRow($result))
{
$resultArray[]=$row;
$rowCount++;
}
New method:
$sql = '
{
query : [
{
table: "table" ,
column: "*",
order: "T_id"
}
]
}
';
$result = $testDB->select($sql);
The results will be returned in an associative array called $result.
After this is done our row count will be available as:
$rowNumber = $testDB->rowCount;
This is only an example and may not be exact. But this is the idea behind the new "proposed" PDO based code I am working on.
The $sql query section will be setup a little different but I have to play around with it a little until I get what I want. Instead of "query" it may state what it is actually doing such as delete, insert, select etc... This makes more sense when we look at transactions...
It may also allow me to simplify the code too and allow for one "query" command using the json style to accomplish everything instead of the current structure based on the PDO4You class that requires 8 separate methods calling 2 other methods.
