SQL Injection

[stig1]

Hey

 

Whats the best method to stop SQL Injection on all values entered into a database that the user gets to create?

 

 

[gevans]

mysql_real_escape_string()

[Jahren]

I personaly use

 

	
function remove_html_tags($chaine)
{
	return htmlentities($chaine, ENT_QUOTES);
}

 

and then I use

 

function put_back_html_tags($chaine)
{
	return html_entity_decode($chaine);
}

 

to read it back

[Xtremer360]

I agree with gevans. I always use mysql_real_escape_string()

[gevans]

Jahren,

 

htmlentities wont escape single and double quotes which are the main issues in sql injection

 

if you only use htmlentites I could probably hack any of your sites

[keiran420]

Jahren,

 

htmlentities wont escape single and double quotes which are the main issues in sql injection

 

if you only use htmlentites I could probably hack any of your sites

 

he used htmlentities($chaine, ENT_QUOTES)

Note ENT_QUOTES...

 

Its an option to encode quotes or not basically, this is the same methord i use, and i havnt got

an injection past it yet..

 

Though no method is 100% secure...

 

And i cant speak for the other option, just my views.

[gevans]

Jahren,

 

htmlentities wont escape single and double quotes which are the main issues in sql injection

 

if you only use htmlentites I could probably hack any of your sites

 

he used htmlentities($chaine, ENT_QUOTES)

Note ENT_QUOTES...

 

Its an option to encode quotes or not basically, this is the same methord i use, and i havnt got

an injection past it yet..

 

Though no method is 100% secure...

 

And i cant speak for the other option, just my views.

 

That is true, but that function isn't built for the purpose of this thread. mysql_real_escape_string() from php.net;

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

[gevans]

Though I do use more than just that, I have a class set up to do a lot of different 'clensing'

 

<?php
var $old_string = array('\r\n', '\n', '\r'), $new_string = '<br />';

function input($input,$url = 1){
	if('get_magic_quotes_gpc') $input = stripslashes($input);
	$input = mysql_real_escape_string($input);
	$input = strip_tags($input);
	$input = str_replace($this->old_string,$this->new_string,$input);
	$input = trim($input);
	if($url) $input = urlencode($input);
	$input = ($input == "") ? NULL : $input;
	return $input; 
}
?>

[keiran420]

Jahren,

 

htmlentities wont escape single and double quotes which are the main issues in sql injection

 

if you only use htmlentites I could probably hack any of your sites

 

he used htmlentities($chaine, ENT_QUOTES)

Note ENT_QUOTES...

 

Its an option to encode quotes or not basically, this is the same methord i use, and i havnt got

an injection past it yet..

 

Though no method is 100% secure...

 

And i cant speak for the other option, just my views.

 

That is true, but that function isn't built for the purpose of this thread. mysql_real_escape_string() from php.net;

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

 

Or be extra safe and whack it through both ^^

 

EDIT

 

Or what the post above me just said XD

Use em all!

 

[gevans]

Haha, exactly. It's not needed but I always urlencode everything, then the little fu**ers are screwed

[corbin]

Y'all take a slightly different approach than I do.

 

 

I only escape ' since it's the only thing (don't go and get all technical on me with this statement) that needs to be escaped (I use mysql_real_escape_string), and then if the HTML needs to be escaped, I escape it when it's echoed out to the user.  Makes more sense to me.

[gevans]

Exactly, everyone has they're own methods, and as long as you're secure happy days!

[Jahren]

I use htmlencode also for portability.

I encode html chars into the database for later use on windows or linux machines without problems ^^

[gevans]

I've been using Rich Text Editors in some bespoke CMS's recently, they are a pain in the ass to work from browser to browser and have to go through a lot of filtering, kind of a similar idea

[ardyandkari]

i use this:

(stripslashes(htmlentities(mysql_real_escape_string($whatever))))

 

maybe it is overboard, but i want all bases covered...twice

[gevans]

That can cause problems sometimes, imagine this

 

from a form - ' OR 1=1

$var = $_POST['form the form'];

 

$var = stripslashes(htmlentities(mysql_real_escape_string($var))));

 

$var is now \\' OR 1=1

 

so the first escape is now escaping the second

 

SELECT * FROM database WHERE this='' OR 1=1

[ardyandkari]

That can cause problems sometimes, imagine this

 

from a form - ' OR 1=1

$var = $_POST['form the form'];

 

$var = stripslashes(htmlentities(mysql_real_escape_string($var))));

 

$var is now \\' OR 1=1

 

so the first escape is now escaping the second

 

SELECT * FROM database WHERE this='' OR 1=1

 

so you recommend just mysql_real_escape_string()?

also, i did have it tested in the beta test area.  darkwater (i think) used actunix or whatever and it didnt come up with any problems...

 

one other question...if using just mysql_real_escape_string(), when you display the data, do you need to use stripslashes() then?

[gevans]

I just realised, you have stripslashes() and mysql_real_escape_string() (though you shouldn't, don't you mean addslashes()?)

 

Using stripslashes(htmlentities(mysql_real_escape_string($var)))) will leave you with the original string, escaped and un-escaped

 

if you meant addslashes(htmlentities(mysql_real_escape_string($var)))) you will have everything escaped twice;

 

$var "'this is my variable'";
echo addslashes(htmlentities(mysql_real_escape_string($var))); //outputs \\\'this is my variable\\\'

 

I'd recommend using just mysql_real_escape_string();

 

If you want to go further by all means use htmlentities() or urlencode() (as well as standard stuff, trim() for example)

[ardyandkari]

no...i dont use addslashes()

 

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

 

it looks like it adds backslashes to those characters...when the entry is applied to the db, is it the actual entry or will it have extra slashes?  or will it just do something like this:

form entry-> ' OR 1=1

database entry -> \' OR 1=1

still confused about injection attacks...read up all over the net and they still go over my head.  sorry.

[corbin]

In MySQL (and most SQL dialects) '' denotes a string, much like it does in PHP.  For example, in PHP, if you wanted to put This is Corbin's Post in a string with double quotes, you would have to do:

 

$string = 'This is Corbin\'s Post';

 

Yes?

 

Well, MySQL parses too.  The MySQL server parses tokens out of the query.  So, similar to in PHP, things need to be escaped.  \ is the escape character in MySQL (and PHP).  So, to send it a value of the mentioned string earlier, one would have to do:

 

SELECT * FROM posts WHERE content = 'This is Corbin\'s post';

 

If it had been SELECT * FROM posts WHERE content = 'This is Corbin's post';

 

s post' would've been considered outside of the string.

 

Now consider this example:

 

SELECT 1 FROM users WHERE username = '$s1' AND password = '$2';

 

Now, let's pretend $s1 is "admin"

 

 

What if someone submitted ' OR 1 = 1;-- as $s2?

 

 

It would turn the query into:

 

SELECT 1 FROM users WHERE username = 'admin' AND password = '' OR 1 = 1;--;

 

Everything after and including -- is ignored, so that would in essence be:

 

SELECT 1 FROM users WHERE username = 'admin' AND password = '' OR 1 = 1;

 

Which would select 1 if the password were '' or if 1 = 1 (always true).

 

 

 

 

 

Oh, to answer your real question, no the actual string won't have the slash.

 

 

Does $string = 'This is Corbin\'s Post'; actually have the slash in it?  Of course not.

[ardyandkari]

thanks.  both for the lengthy explanation and also the answer

 

the idea of injection is still past me, but i didnt know that \ was the escape character for mysql.

[corbin]

What do you not get about it?  You're probably over complicating it.  It's really not a difficult concept once you see it the first time.

[ardyandkari]

Now consider this example:

 

SELECT 1 FROM users WHERE username = '$s1' AND password = '$2';

 

Now, let's pretend $s1 is "admin"

 

What if someone submitted ' OR 1 = 1;-- as $s2?

 

It would turn the query into:

 

SELECT 1 FROM users WHERE username = 'admin' AND password = '' OR 1 = 1;--;

 

Everything after and including -- is ignored, so that would in essence be:

 

SELECT 1 FROM users WHERE username = 'admin' AND password = '' OR 1 = 1;

 

Which would select 1 if the password were '' or if 1 = 1 (always true).

 

basically i dont understand how the OR 1 = 1; portion works.  this is assuming that this is a login, right?

so what happens is in the user name text field you enter < admin > and in the password box you enter < ' OR 1 = 1; > (<>denotes the text box, you dont actually type it.)

 

now this is assuming that you know what the user name is, but if one isnt sanitized, all probably aren't right?  so you could use the same in both? 

 

i do understand that 1 = 1 is always true, but how does that affect the login?  does that automatically log you in?

[ardyandkari]

btw... i am posting a link to a little program that i was working on...

 

http://everkleen.biz/resort/literes/index.php

 

i used the full <<stripslashes(htmlentities(mysql_real_escape_string($var))))>> sanitize string that i posted.

 

the username is test

the password is d

if you want to look around, you can...not finished yet though.  try injecting.  like i said, it was tested at one point and it passed...

 

[gevans]

That page seems pretty safe after a little play, but I'm not sure why, lol

 

stripslashes(htmlentities(mysql_real_escape_string($var))) is not a good thing to do, ur excaping and stripping slashes so you end up with the original string