Page 1 of 1

Best Way To Prevent SQL Injection

Posted: Fri May 09, 2008 11:34 pm
by johnsmith153
I want to ensure no SQL attacks and the entire process of passing/receiving from the dbase is secure. All the security with quote, double quote stuff scares the hell out of me.

I also here stories in a mysql table where people can do things like add:
== droptable
to the end of your script and it deletes the table or something.

I fully understand how people can do SQL injection, just need advice on what is the best way I should prevent it:

My current method (not coded yet, planning first) removes a lot of useability, but I think it will make security very good.

Submitting data to database: (assuming for a message board)
1. Reject if characters entered other than A-Z a-z 0-9 ! . ; , ’ : ) ? £ @ + - = # ^ (i.e cant enter ")
2. use str_replace("\'", "'", $name); to replace the ' with \' (escape it but my method may be a little different)
And obviously reverse it before displaying again.
I cant use the mysql_real_escape_string() as I am using Caspio Bridge Web Services and not MySQL for this project. A database web services API.
I wont use Magic Quotes, addslashes / stripslashes as this is deprecated from php 6

What about htmlentities??

Re: Best Way To Prevent SQL Injection

Posted: Sat May 10, 2008 1:16 am
by RobertGonzalez
This is an enormously loaded question that could easily span many books, many web sites and many tutorials.

I would recommend picking up a book or two, specifically from the likes of guys like Chris Shiflett, Stefan Esser and Ilia Alshanetsky, and read them carefully for their recommendations.

Re: Best Way To Prevent SQL Injection

Posted: Sat May 10, 2008 2:46 am
by Mordred
Check your SQL database provider documentation on how to correctly escape input!
Do not make your own escape function, you will get it wrong.

Pitfalls

Re: Best Way To Prevent SQL Injection

Posted: Sat May 10, 2008 3:18 am
by Apollo
For starters, I can recommend Mordred's paper.
Boils down to: (with some added wisdom of my own :))

1. For string values, escape all characters necessary for your particular database. For MySQL, mysql_real_escape_string() does this by escaping \x00, \x1a \n, \r, ', ", \, and for wildcard strings (in LIKE clauses) you should also escape % and _.
This can be done with a simple str_replace function, just make sure which characters need to be escaped for your DB.

2. For numeric values, always use intval() for integers or floatval() for floats.

3. If you're using a string value as a column name (i.e. "order by ..."), never trust it and restrict to an array of allowed identifiers (see example B in Mordred's paper).

4. Additionally, prior to using or validating any user input, make sure it's not an array when it's not supposed to be. Users could submit arrays on the URL where you expect normal values (strings, integers, etc) causing most checks to generate PHP errors.

Like already mentioned, this is a complex topic, but the above guidelines should give you a good start and nullify most vulnerabilities.


(edit) oh, too late.. Mordred already posted himself :) Well hopefull the above summary is of some use to ya

SQL INJECTION / HTML ENTITIES / FORM CHECKING

Posted: Sat May 10, 2008 2:12 pm
by johnsmith153
FULL CHECKS BEFORE ADDING TO DATABASE – SQL INJECTIONS, HTML TAGS – THE LOT

Ok, I have taken yours and others' advice and this is my final effort if you could confirm it is good enough, that would be great.

A few answers would be great:

(1) Is below suitable to ensure very good security on my site? Do I need anything else?
(2) I am using <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> for ALL pages. There is a bit with htmlentities where I have selected 'UTF-8' – Is this correct?
(3) Will I be covered by people trying to enter the following?
-- (I have heard putting this can signal to end a command)
;
=
\
+
Entering char()
Using &# to display html characters
ASCII / Binary or something?

I cant / wont use:
mysql_real_escape_string (I am using a MySQL database, but through a web services API – so doesn't work)
stripslashes / addslashes / magic_quotes (deprecated php 6)

Imagine for this example a user is submitting a message to a message board. And for example's sake, a form posts the message, name of user and age of user (assume no log in – they just type in their name, age and message.)

I will always use fixed column names and never dynamically create them (i.e I will never use ORDER BY '$columnname' ) – however I will often use dynamic statements (i.e I WILL use - Customer LIKE '$customername')

PREPARE DATA TO ADD TO DATABASE

<?php

global $error_chk;
$error_chk = 0;
function checkdata($value, $valtype, $length)
{

global $error_chk;

$value = trim($value);
$value = htmlentities($value, ENT_NOQUOTES, 'UTF-8');
$value = str_replace("#", "\#", $value);
$value = str_replace("%", "\%", $value);
$value = str_replace("_", "\_", $value);
$value = str_replace("\x00", "\\x00", $value);
$value = str_replace("\n", "\\n", $value);
$value = str_replace("\r", "\\r", $value);
$value = str_replace("\\", "\\\\", $value);
$value = str_replace("'", "\'", $value);
$value = str_replace("\"", "\\\"", $value);
$value = str_replace("\x1a", "\\\x1a", $value);

if($valtype=="st"){if(!is_string($value)){$error_chk = 1;$value="";}}
if($valtype=="ar"){if(!is_array($value)){$error_chk = 1;$value="";}}
if($valtype=="in"){if(!is_numeric($value))
{$error_chk = 1;$value="";}else{intval($value);}}
if($valtype=="fl"){if(!is_numeric($value))
{$error_chk = 1;$value="";}else{floatval($value);}}

if($valtype=="st"){if(strlen($value)>$length){$error_chk=2;$value="";}}
if($valtype=="in"){$value>$length){$error_chk=2;$value="";}}

}

if($error_chk!=0)
{
echo "DONT ADD TO DB";
}

//Now I can do this: (I think)
//st=string in=integer etc.
$messagetoadd = checkdata($_POST['message'], 'st', 250);
$name = checkdata($_POST['name'], 'st', 16);
$age = checkdata($_POST['age'], 'in', 110);

?>


PREPARE DATA TO SHOW ON SCREEN TO USER

Reverse the above
i.e. use: html_entity_decode($value, ENT_NOQUOTES, 'UTF-8');


Thanks for any help.

Re: Best Way To Prevent SQL Injection

Posted: Sat May 10, 2008 2:34 pm
by RobertGonzalez
For dynamic queries try to use prepared statements or stored procedures.

Re: Best Way To Prevent SQL Injection

Posted: Sat May 10, 2008 5:18 pm
by johnsmith153
Is that a no to my script then?

All of it? Some of it?

I know it is a long winded message, but I wanted to explain to prevent you guys from having to ask questions to get the info you needed.

Re: Best Way To Prevent SQL Injection

Posted: Sat May 10, 2008 5:43 pm
by johnsmith153
Everah | Please use the correct bbCode tags when posting code in the forums. You can use [code], [php] or [syntax="{lang}"] where {lang} is the language you want to highlight in, including php, html, css and sql.

To all, if this is too long to read, just tell me if this would do the job to validate input before entry to database:

Code: Select all

<?php
 
global $error_chk;
$error_chk = 0;
function checkdata($value, $valtype, $length)
{
 
global $error_chk;
 
$value = trim($value);
$value = htmlentities($value, ENT_NOQUOTES, 'UTF-8');
$value = str_replace("#", "\#", $value);
$value = str_replace("%", "\%", $value);
$value = str_replace("_", "\_", $value);
$value = str_replace("\x00", "\\x00", $value);
$value = str_replace("\n", "\\n", $value);
$value = str_replace("\r", "\\r", $value);
$value = str_replace("\\", "\\\\", $value);
$value = str_replace("'", "\'", $value);
$value = str_replace("\"", "\\\"", $value);
$value = str_replace("\x1a", "\\\x1a", $value);
 
if($valtype=="st"){if(!is_string($value)){$error_chk = 1;$value="";}}
if($valtype=="ar"){if(!is_array($value)){$error_chk = 1;$value="";}}
if($valtype=="in"){if(!is_numeric($value))
{$error_chk = 1;$value="";}else{intval($value);}}
if($valtype=="fl"){if(!is_numeric($value))
{$error_chk = 1;$value="";}else{floatval($value);}}
 
if($valtype=="st"){if(strlen($value)>$length){$error_chk=2;$value="";}}
if($valtype=="in"){$value>$length){$error_chk=2;$value="";}}
 
}
 
if($error_chk!=0)
{
echo "DONT ADD TO DB";
}
 
//Now I can do this: (I think)
//st=string in=integer etc.
$messagetoadd = checkdata($_POST['message'], 'st', 250);
$name = checkdata($_POST['name'], 'st', 16);
$age = checkdata($_POST['age'], 'in', 110);
 
?>
Everah | Please use the correct bbCode tags when posting code in the forums. You can use [code], [php] or [syntax="{lang}"] where {lang} is the language you want to highlight in, including php, html, css and sql.

Re: Best Way To Prevent SQL Injection

Posted: Sun May 11, 2008 1:15 am
by Mordred
No.
No no no no no.
Do not, and I can't stress it enough with bbcode, do not do it yourself.
Not only it's wrong, but you haven't even tested it.
It "returns" value through a global var - check the manual on functions, they can return values.
It tries to escape for HTML and SQL which is the first sign of not doing it right.

For html - htmlspecialchars($value, ENT_QUOTES, 'UTF-8')
For SQL - if the API you use offers interface to make SQL queries, then it should have an interface for escaping values as well. If not, drop the API. If not, whine to their support until they add the interface.

If you're 100% sure that this is MySQL and the database uses UTF-8 charset (not your HTML, the remote database), you may use mysql_escape_string(). Only on these two conditions, you were warned. But the best solution is the above - look in the documentation for the API.