I've been developing in PHP for around a month now and realised that it would be good to have a script on-hand that can handle all my SQL queries. I started this script around 3 days ago as a means for myself to have injection-free SQL without embedding functions and SQL in my page(s).
I've seen other people write functions similar to this and am dissatisfied with the results. After working on this for a few hours I decided that it would not be a huge effort to create a script that anyone familiar with SQL syntax can use on their website.
My questions are; has this kind of thing been done before, like this? Would anyone be interested in using such a script? What kind of features would you like to see?
The current version is 0.3.1 and I'm busy building support for INSERT, UPDATE, and DELETE.
This was version 0.1.0:
Code: Select all
class dal
{
public $shoes;
private $dbhost;
private $dbuser;
private $dbpass;
public function execSQL($fields, $from, $join, $field, $on, $refs, $values)
{
// Let's build a SQL string!
$this->buildSQL($fields, $from, $join, $field, $on, $refs, $values);
// Connect to MySQL
mysql_connect($this->dbhost, $this->dbuser, $this->dbpass) or die('<b>Page Error: </b>Could not connect to the mysql server. '.mysql_error());
mysql_select_db('igal') or die('<b>Page Error: </b>Could not select Database. '.mysql_error());
// result and return variables
$result = mysql_query($this->sql) or die('<b>Page Error: </b>Could not query the database. '.mysql_error().'<br />'.$this->sql);
$row = mysql_fetch_array($result, MYSQL_NUM);
// Free the result and return the value
mysql_free_result($result);
return $row;
}
// This function builds the sql string and then passes it to the class
private function buildSQL($fields, $from, $join, $field, $on, $refs, $values)
{
// This is all very self-explanatory
$this->innerJOIN($from, $join, $field, $on);
$this->getSELECT($fields, $from);
$this->getWHERE($refs, $values);
$this->sql = $this->select.$this->inner.$this->where;
}
// This function builds the SELECT part of the sql string
private function getSELECT($fields, $from) # array $fields
{
// Let's make sure there are no duplicate keys in our array
$fields = array_unique($fields);
// Now let's turn it into a comma-delimited list
$fields = implode(", ", $fields);
// Build the SELECT statement
$this->select = 'SELECT '.$fields.' FROM ';
if ($this->shoes == 'dull') { $this->select .= $from.' '; }
}
// This function provides the object with the WHERE part of the sql string
public function getWHERE($refs, $values) # array $refs, array $values
{
// Does the user want a WHERE statement?
if (empty($refs) && empty($values)) { return; }
$this->where = ' WHERE ';
// Firstly and most importantly we are going to hand off the $values array
// to another function, which will parse the values and then return them
// to the getWHERE function, this is all to prevent SQL injection attacks
$values = $this->SQLInjectionSucks($values);
// Second let's make sure the number of keys in each of our arrays match
$refCount = count($refs);
$valueCount = count($values);
if ($refCount != $valueCount) { die('$refs and $values arrays have different numbers of keys'); } else { $count = $refCount; unset($refCount); unset($valueCount); }
// Now let's add parentheses to our WHERE statement
$this->where .= '(';
for ($i = 0; $i < $count; $i++)
{
$this->where .= '(('.$refs[$i].')='.$values[$i].')';
if ($i != $count-1) { $this->where .= ' AND '; }
}
$this->where .= ')';
}
// This function, which is by far my favorite, provides the object
// with all the information needed to perform any INNER JOIN's
private function innerJOIN($from, $join, $field, $on) # string $from, array $join, array $field, array $on
{
// We should make sure that the user wants to do an INNER JOIN
if (empty($join) && empty($field) && empty($on)) { return; }
// Let's make sure there are no duplicate keys in our arrays
$join = array_unique($join);
$field = array_unique($field);
$on = array_unique($on);
// Now we should count the number of keys in each array and make sure they match
$joinCount = count($join);
$fieldCount = count($field);
$onCount = count($on);
// If they don't match we should probably alert the user
if ($joinCount != $fieldCount && $fieldCount != $onCount) { die('$join, $field, $on arrays have different numbers of keys, SQL cannot execute.'); } else { $count = $joinCount; unset($joinCount); unset($fieldCount); unset($onCount); }
// This says 'add a parentheses for every join but the last one'
for ($i = 0; $i < $count-1; $i++) { $this->inner .= '('; }
// Now we add the table for the FROM statement
$this->inner .= $from.' ';
// Finally we add the inner join to the string
for ($i = 0; $i < $count; $i++)
{
$this->inner .= 'INNER JOIN '.$join[$i].' ON '.$field[$i].' = '.$on[$i];
if ($i != $count-1) { $this->inner .= ') '; }
}
$this->inner .= ' ';
$this->shoes = 'shiny';
}
// This function is small, but very important. it will take an array of values
// then parse them, removing any bad SQL that could be an injection attack
function SQLInjectionSucks($values)
{
$search = array('/INSERT/', '/UPDATE/', '/DELETE/', '/FROM/', '/VALUES/', '/\*/', '/ /', '/\;/', '/\'/', '/\"/', '/--/');
$replace = '';
return preg_replace($search, $replace, $values);
}
// I'm still not quite sure what a construct is, but the class needs one
// in order for it to hold all the variables it needs to build the sql stirng
function __construct() {
$this->shoes = 'dull';
$this->dbhost = cSQLHost;
$this->dbuser = cSQLUser;
$this->dbpass = cSQLPass;
$this->inner = '';
$this->select = '';
$this->where = '';
$this->sql = '';
}
}I would really like input from other developers on this script before I continue it. I'm relatively new to programming and constantly wonder if I'm doing a decent job.
Thanks for your time!