Code Review - SQL and Insertion Attacks (Warning: Not for Newbs)

Posted on 16th Feb 2014 07:03 pm by admin

Hey guys,

Its been a while, I know. Use to love coming here to answer peoples questions, but work and school have been keeping me too busy but do anything but lurk. I usually write all my code/apps by hand and over the past couple years created a common set of PHP functions that I implement on every site. This code continues to grow, and may become a framework one day (http://ryan.crawford.com/framework/default/). I wanted to present some of the crux functions for your consideration. Specifically I'd like to know what your best practices are in relation to preventing SQL insertion attacks, looking at the functions I use to control it:

Code: private $chars = array(
";" => "{00sc}", "'" => "{01sq}",
"!" => "{02ex}", "$" => "{03dl}",
"%" => "{04pr}", "<" => "{05ls}",
">" => "{06gt}", "=" => "{07eq}",
"&" => "{08an}", "#" => "{09pd}",
"," => "{10cm}", "/" => "{11fs}",
"*" => "{12as}", "\"=> "{13bs}"
);

/*
* Func: inject($str) - aptly named :)
* Desc: We'll be the only people doing SQL injection here
*/
function inject($str) {
return str_replace(array_keys($this->chars),
array_values($this->chars),$str);
}

/*
* Func: extract($str)
* Desc: Opposite of inject
*/
function extract($str) {
$str = str_replace(array_values($this->depc),
array_keys($this->depc),$str);

return str_replace(array_values($this->chars),
array_keys($this->chars),$str);
}

/*
* Func: query($query_data)
* Desc: Make a query on the database (SELECT)
* Note: If a log directory is defined, we will track queries
*/
function query($qdata) {
$result = mysql_query($qdata) or die("
Query: ".$qdata."

Issue: " . mysql_error());

// set the condition for the switch statement
$c = substr($qdata,0,strpos($qdata,' '));

if($c == "DELETE"||$c == "INSERT"||$c == "UPDATE") {
if(is_dir($this->cfg['logdir']))
$this->logLine($qdata,$this->cfg['qlog']);
return true;
}
if(mysql_num_rows($result)==0)
return false;

while($line = mysql_fetch_array($result,MYSQL_ASSOC)) {
$array_result[]=$this->extract($line);
}
return $array_result;
}

/*
* Func: iquery($array,$table)
* Desc: Insert data into the db(using just $_POST)
*/
function iquery($arr,$table) {
if(!$dataArr = $this->againstTable($arr,$table))
return false;
$n = 1;
// Loop to create SQL query
foreach($dataArr as $key => $value) {
$insertNames .= (sizeof($dataArr)==$n)? $key : $key.",";
$insertValues.= (sizeof($dataArr)==$n)? "'".$value."'" : "'".$value."',";
$n++;
}
$this->query("INSERT INTO ".$table." (".$insertNames.") VALUES (".$insertValues.");");
}


Basically, if you look at the array $chars, for inserted data I am searching for the keys and converting them to the values before insertion. It is a function I run all my inserts through. The query() function is what I do all my SELECTS with and it converts the characters back (the inject function encrypts, extract decrypts for lack of a better term). These are written object oriented so I still have access to PHP's extract if ever needed.

Question now being, what do you think of the effectiveness of this technique in terms of protecting against SQL injection? Are there ways to beat it? Are there better methods? Is this efficient? I'm not sure what algorithm PHP is using for str_replace, but the best ones don't run any faster than O(n). I don't think this runs any worse.

Your Answer:

Login to answer

Other forums

LSB (PHP 5.3) problem with static value!
hello,

i'm having a problem. static::$text variable gets lost at some point. can someone plea

unserialize help, getting errors
hi all ,
I got a form with 6 fields (2 input and 4 select box), when the page loads one block is

subtract 1 from value entered in text field
Hi

How do I subtract 1 from the vaue entered into a text field?

Thanks

Ignore html and bbcode?
I've created a function to limit the amount of text according to the amount of words.

However

Filling gaps in dates
So i'm doing a query for sales data and my return array looks like this

('date' => '20

does anyone know a php script to send mail using gmails/googles free smtp server
cause i heard u can do that but i dont know how

PHP Login
Hey!
I got this shopcart code online, am trying to modify it but am getting an error when i try a

Form submissing with PHP and JQuery/Ajax
I have searched everywhere, but cannot find a solution for this... I have worked all day trying to g

Need help/w Page: 1,2,3,4 function!
So I have this code im working on where it's ment to display seperate pages having 20 logs each
I

Take info from one coloum and move to another
Hi all,

I have this:
Code: [Select]$array = "SELECT stock_id FROM stocks WHERE stock_