Building query cleanup together

[Pr0t0n]

Hi guys,

 

I am trying to build a mysql function that handles building up querys for the dbase, it all works so far.

I started at a function inside that class that handles cleaning up the arrays so it can be sent to the dbase

without having to worry about injections, I wanted to write it myself but I thought if you guys have lines to

add please leave it in a comment and I will update the code, I will also make it open source.

function QueryCleaner($query12) {
        
	trim($query12);
        
	if (preg_match ('/\bUNION\b|\bJOIN\b|1=1/i', $query12)) return null; //Checking for SQL injections, so deny them
	if (substr_count ($query12, "'") % 2 == 1) return null; //something with an uneven number of quotes, could be SQL injection
        
	$query =(get_magic_quotes_gpc()) ? stripslashes($query) : $query;  
        return mysqli_real_escape_string($query);
        
    }

If you got ideas for this let me know!

[ginerjm]

Instead of trying to write such a complex class, why not just use prepared statements?  Trying to build a query statement for any and all circumstances would be an impossible task.  Besides, the time it takes you write individual queries would never add up to the time it is going to take you to perfect such a thing (if even possible).

[mogosselin]

Like ginerjm said, you should use prepared statements or PDO. Do not try to write your own code to prevent SQL injections. That can be a fun side project, but I would not use that in production code.

A rule of thumb about security in Web applications: Never try to write your own security code. You'll lose time reinventing the wheel and worse, you'll probably leave massive security holes. 

[Pr0t0n]

Ok ok I get that, I do not completely agree that its not possible or taking massive amounts of time, also I never coded for production appz, trying to go in that direction right now but thats for another topic.

 

Both of you thanks, for the replys, mogosselin I am gonna look into prepared statements.

 

Also:

 

http://www.tero.co.uk/scripts/superdatabase.php

 

Not that I am gonna use it.

 

And about 10 years ago when I started with perl I learned to code everything myself. So I rather stop coding at all then using and editing others code.

BUT... purely for security risks I understand that certain parts might need other code.

 

I can write on and on about this, I understand both comments I am gonna look it up and if its better then the idea I got about the class I will implement it.

[Pr0t0n]

Ok I read enough to say I was wrong, but how long does this function exists lol I only remember the time were u had to code something like that.

 

But still this is only useful when u got the queries and my class handles that and only at the end I could implement it for further use.

 

Only this I dont completely get its from php.net

 

"If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)."

 

The bold part is what I dont understand maybe someone can give me an example?

[mac_gyver]

that refers to the parts of a query that are not data values, i.e. things like table names, column names, sort directions, ... any sort of identifiers or keywords that are being built dynamically by php code/variables.

 

these things are not data values, cannot be bound into a prepared query, and using string escape functions won't prevent sql injection in them because they are not used in a query as string data. they must be validate to insure they contain only expected content in order to prevent sql injection.

[Pr0t0n]

Aha... well I think I understand what you mean. However I havent been able to find a good guide on the subject outside php.net I want to completely understand those prepared statements before I adapt the code.

 

Maybe someone has a guide that covers alot?

Edited March 2, 2014 by Pr0t0n