[Psybadek]

I dont know why, but now that I'm taking the time to secure my script I really want to know, is all I need to do to prevent it is to just wrap whats being inputted in mysql_real_escape_string?

[End User]

Originally Posted by Psybadek

I dont know why, but now that I'm taking the time to secure my script I really want to know, is all I need to do to prevent it is to just wrap whats being inputted in mysql_real_escape_string?

Trust me, mysql_real_escape_string() is easily bypassed. It will NOT keep you safe and that's a fact. Check here for lots of examples that will easily bypass mysql_real_escape_string():

http://ha.ckers.org/xss.html

If you can protect against all of those examples, you're *probably* safe (but I wouldn't count on it).

If you can't protect against all of those, you aren't safe.

mysql_real_escape_string() is like an airbag that always works right up until it's needed.

[Psybadek]

OK, so is there any good tutorials on preventing sql injection? I figured it couldn't be that simple.

[End User]

Well, you could Google for "preventing sql injection" but that would almost be like cheating.

[Psybadek]

I have but all they tell me to do is use mysql_real_escape_string

[End User]

Originally Posted by Psybadek

I have but all they tell me to do is use mysql_real_escape_string

Out of 4.6 million results, I'd bet there's one that doesn't suggest mysql_real_escape_string() is the ultimate solution.

You could always use something like this function:

PHP Code:

<?php

////////////////////////////////////////////
// input sanitizer function - LDM 2008

function sanitize($dtype, $dlen, $data){

// dtype 1: allow numbers, space, and '-' 
// dtype 2: allow alpha and spaces only
// dtype 3: allow alphanumeric, spaces, period, and '-'
// dtype 4: allow alphanumeric w/ all punctuation 
// dtype 5: email validation chars 
// dlen: data length limit, '0' = no length limit 

    // special cleanups
    $data = preg_replace("/x1a/",'', $data);
    $data = preg_replace("/x00/",'', $data);

    // the 2 tests above may not be needed due to this more complete test
    $data = preg_replace('/([\x00-\x08][\x0b-\x0c][\x0e-\x20])/', '', $data);

    $data = preg_replace("|\.\./|",'', $data); // stop directory traversal
    $data = preg_replace("/--/",' - ', $data); // stop mySQL comments
    $data = preg_replace("/%3A%2F%2F/",'', $data); // stop B64 encoded '://'

    
// new, added 8-31-2008 /////////////////////////////////
////////// START NEW TESTS 08-31-2008 ////////////////////////////////////////

// Remove Null Characters
// This prevents sandwiching null characters
// between ascii characters, like Java\0script.
    $data = preg_replace('/\0+/', '', $data);
    $data = preg_replace('/(\\\\0)+/', '', $data);

 
// Validate standard character entities
// Add a semicolon if missing.  We do this to enable
// the conversion of entities to ASCII later.
    $data = preg_replace('#(&\#*\w+)[\x00-\x20]+;#u',"\\1;",$data);
        
// Validate UTF16 two byte encoding (x00)
// Just as above, adds a semicolon if missing.
    $data = preg_replace('#(&\#x*)([0-9A-F]+);*#iu',"\\1\\2;",$data);


// URL Decode
// Just in case stuff like this is submitted:
// <a href="http://%77%77%77%2E%67%6F%6F%67%6C%65%2E%63%6F%6D">Google</a>
// Note: Normally urldecode() would be easier but it removes plus signs
    $data = preg_replace("/([a-z0-9]{3})/i", "&#x\\1;", $data);
    $data = preg_replace("/%([a-z0-9]{2})/i", "&#x\\1;", $data);        
                

// Convert character entities to ASCII
// This permits our tests below to work reliably.
// We only convert entities that are within tags since
// these are the ones that will pose security problems.
    if (preg_match_all("/<(.+?)>/si", $data, $matches)) {        
        for ($i = 0; $i < count($matches['0']); $i++) {
            $data = str_replace($matches['1'][$i],
                html_entity_decode($matches['1'][$i], ENT_COMPAT, $charset), $data);
        }
    }
    

// Convert all tabs to spaces
// This prevents strings like this: ja    vascript
// Note: we deal with spaces between characters later.    
    $data = preg_replace("#\t+#", " ", $data);
    

// Makes PHP tags safe
// Note: XML tags are inadvertently replaced too:
//    <?xml
// But who cares, only terrorists use XML. :)     
    $data = str_replace(array('<?php', '<?PHP', '<?', '?>'),  array('&lt;?php', '&lt;?PHP', '&lt;?', '?&gt;'), $data);
    

// Compact any exploded words
// This corrects words like:  j a v a s c r i p t
// These words are compacted back to their correct state.    
    $words = array('javascript', 'vbscript', 'script', 'applet', 'alert', 'document', 'write', 'cookie', 'window');
    foreach ($words as $word) {
        $temp = '';
        for ($i = 0; $i < strlen($word); $i++) {
            $temp .= substr($word, $i, 1)."\s*";
        }
    
        $temp = substr($temp, 0, -3);
        $data = preg_replace('#'.$temp.'#s', $word, $data);
        $data = preg_replace('#'.ucfirst($temp).'#s', ucfirst($word), $data);
    }


// Remove disallowed Javascript in links or img tags    
    $data = preg_replace("#<a.+?href=.*?(alert\(|alert&\#40;|javascript\:|window\.|document\.|\.cookie|<script|<xss).*?\>.*?</a>#si", "", $data);
    $data = preg_replace("#<img.+?src=.*?(alert\(|alert&\#40;|javascript\:|window\.|document\.|\.cookie|<script|<xss).*?\>#si","", $data);
    $data = preg_replace("#<(script|xss).*?\>#si", "", $data);

// Remove JavaScript Event Handlers
// Note: This code is a little blunt.  It removes
// the event handler and anything up to the closing >,
// but it's unlikely to be a problem.

    $data = preg_replace('#(<[^>]+.*?)(onabort|onactivate|onafterprint|onafterupdate|onbeforeactivate|onbeforecopy|onbeforecut|onbeforedeactivate|onbeforeeditfocus|onbeforepaste|onbeforeprint|onbeforeunload|onbeforeupdate|onblur|onbounce|oncellchange|onchange|onclick|oncontextmenu|oncontrolselect|oncopy|oncut|ondataavailable|ondatasetchanged|ondatasetcomplete|ondblclick|ondeactivate|ondrag|ondragend|ondragenter|ondragleave|ondragover|ondragstart|ondrop|onerror|onerrorupdate|onfilterchange|onfinish|onfocus|onfocusin|onfocusout|onhelp|onkeydown|onkeypress|onkeyup|onlayoutcomplete|onload|onlosecapture|onmousedown|onmouseenter|onmouseleave|onmousemove|onmouseout|onmouseover|onmouseup|onmousewheel|onmove|onmoveend|onmovestart|onpaste|onpropertychange|onreadystatechange|onreset|onresize|onresizeend|onresizestart|onrowenter|onrowexit|onrowsdelete|onrowsinserted|onscroll|onselect|onselectionchange|onselectstart|onstart|onstop|onsubmit|onunload)[^>]*>#iU',"\\1>",$data);


// Sanitize naughty HTML elements
// If a tag containing any of the words in the list
// below is found, the tag gets converted to entities.   
// So this: <blink>
// Becomes: &lt;blink&gt;    
    $data = preg_replace('#<(/*\s*)(alert|vbscript|javascript|applet|basefont|base|behavior|bgsound|blink|body|embed|expression|form|frameset|frame|head|html|ilayer|iframe|input|layer|link|meta|object|plaintext|style|script|textarea|title|xml|xss|lowsrc)([^>]*)>#is', "&lt;\\1\\2\\3&gt;", $data);
            

// Sanitize naughty scripting elements
// Similar to above, only instead of looking for
// tags it looks for PHP and JavaScript commands
// that are disallowed.  Rather than removing the
// code, it simply converts the parenthesis to entities
// rendering the code un-executable.
// For example:    eval('some code')
// Becomes:        eval('some code')
    $data = preg_replace('#(alert|cmd|passthru|eval|exec|system|fopen|fsockopen|file|file_get_contents|readfile|unlink)(\s*)\((.*?)\)#si', "\\1\\2(\\3)", $data);
                                            
// Final clean up
// This adds a bit of extra precaution in case
// something got through the above filters
    $bad = array(
            'document.cookie'    => '',
            'document.write'    => '',
            'window.location'    => '',
            "javascript\s*:"    => '',
            "Redirect\s+302"    => '',
            '<!--'            => '&lt;!--',
            '-->'            => '--&gt;'
    );
    
    foreach ($bad as $key => $val)    {
            $data = preg_replace("#".$key."#i", $val, $data);
    }

////////// END NEW TESTS /////////////////////////////////////////////////////



    if($dlen != '0'){
        $data = substr($data, 0, $dlen);
    }

    if($dtype == '1'){
        // allow only numeric characters, space, period, and '-' 
        $data = preg_replace("/[^0-9\-\ \.]/",'', $data);
    }
    
    if($dtype == '2'){
        // allow only alpha characters, '_' and space 
        $data = preg_replace("/[^a-zA-Z~\ \_]/",'', $data);
    }
    
    if($dtype == '3'){
        // allow only alphanumeric characters, space, '_', period, colon, and '-'
        $data = preg_replace("/[^0-9a-zA-Z~\-\ \.\:\_]/",'', $data);
    }
    
    if($dtype == '4'){
        // allow only alphanumeric characters w/ punctuation + carriage returns
        $data = preg_replace("|[^0-9a-zA-Z~@#$%=:;_, \\n\\\!\^&\*\(\)\-\+\.\?\/\'\"]|",'', $data);
    }

    if($dtype == '5'){
        // specifically for email validation 
        $data = preg_replace("|[^0-9a-zA-Z@_\-\.]|",'', $data);
    }

    $data = trim($data);

    return $data;
}
// end sanitize 
////////////////////////////////////////////


?>

[therocket954]

That is fantastic. Thanks End User.

I've been using a combination of preg_match(), mysql_real_escape_string(), and a bunch of if statements... this is much cleaner

[End User]

Originally Posted by therocket954

That is fantastic. Thanks End User.

I've been using a combination of preg_match(), mysql_real_escape_string(), and a bunch of if statements... this is much cleaner

You're welcome, and I'm glad you like it. I can't take full credit for it, however- I started with some code scavenged from CodeIgniter and added some additional tweaks, checks, and functionality. I think it's reasonably safe. If someone can find a way to spoof that function, I'd be proud to have them hijack my server and make it part of their botnet. .

[sushi4664]

Woah...impressive function...I am about to borrow that. How can I attribute it? (I hate to take functions other people wrote without stating that I didn't write it)

[Psybadek]

so your saying your not going to give credit for the function?