NamePros
Welcome, Guest! Ready to make a name for yourself in the domain business? We welcome both the hobbyist and professional domainer to join the discussion as part of the NamePros community.

Click here to create your profile to start earning reputation for posting, and trader ratings for buying & selling in our free e-marketplace. Build your trader rating with each successful sale. Our system has tracked over 100,000 sales and counting!
FAQ & TOS Register Search Today's Posts Mark Forums Read

Go Back   NamePros.com > Website Development Discussion Forums > Programming > CODE
Reload this Page SQL Injection Prevention

CODE This forum is for posting code snippets and example scripts that aren't quite tutorials, but could be useful for others. You may post code snippets and/or completed scripts that you've written and want to share here.

Advanced Search


Closed Thread
 
LinkBack Thread Tools
Old 07-22-2005, 07:40 AM THREAD STARTER               #1 (permalink)
Account Closed
 
axilant's Avatar
Join Date: May 2004
Location: /etc/passwd
Posts: 2,178
axilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to behold
 

Member of the Month July 2005

SQL Injection Prevention

Code:
PHP Code:
if (get_magic_quotes_gpc())
{
   $_POST = array_map('stripslashes_deep', $_POST);
   $_GET = array_map('stripslashes_deep', $_GET);
   $_COOKIE = array_map('stripslashes_deep', $_COOKIE);
}
$_POST = array_map('addslashes_deep', $_POST);
$_GET = array_map('addslashes_deep', $_GET);
$_COOKIE = array_map('addslashes_deep', $_COOKIE);
????: NamePros.com http://www.namepros.com/code/108827-sql-injection-prevention.html

function stripslashes_deep($value)
{
    $value = is_array($value) ? array_map('stripslashes_deep', $value) : stripslashes($value);  return $value;
}

function addslashes_deep($value) 
{
    $value = is_array($value) ? array_map('addslashes_deep', $value) : stripslashes($value); return $value;
} 
How to use this code:
Basicly this code is probably good to be put in a config file. Reason for this code is so you dont have to worry about SQL Injection prevention what so ever in your coding, cause its already done in your config file. When you pull records from mysql or something, you will need to do something like this:

PHP Code:
$sql = mysql_query("SELECT * FROM table");
????: NamePros.com http://www.namepros.com/showthread.php?t=108827
$row = mysql_fetch_array($sql);
$row = array_map('stripslashes_deep', $row); //strip the slashes that were added before. 
That way you remove the \" \' ect....

To learn more about php scecurity look at this PHP Security Magazine:
http://www.insecuremagazine.com/INSECURE-Mag-2.pdf
This has a GREAT article about PHP security and keeping your programming secure.

My reason for not using mysql_real_escape_string(), well a lot of people dont even know this function exists, and addslashes/stripslashes is more common. But yet, they do almost the same exact thing. Just a new name, and this is how most big websites do it, you can even take a look at most of the popular forum software, they use addslashes/stripslashes.


What is SQL injection:
Quote:
SQL injection is a security vulnerability that occurs in the database layer of an application. Its source is the incorrect escaping of variables embedded in SQL statements.

Assuming the following code is embedded in the application, and a parameter "userName" that contains the user's name is given, SQL Injection is possible:

statement := "SELECT * FROM users WHERE name = '" + userName + "';"

If supplied with "a'; DROP TABLE users; SELECT * FROM data WHERE name LIKE '%" as "userName", the following SQL statement would be generated:

SELECT * FROM users WHERE name = 'a'; DROP TABLE users; SELECT * FROM data WHERE name LIKE '%';

The database would execute the statement in order, selecting data, dropping (deleting) the "users" table and selecting data that maybe was not meant to be displayed to web users. In essence, any data in the database available to the user connecting to the database could be read and/or modified.
I got this from Wikipedia.

Cody
Last edited by axilant; 10-03-2005 at 01:39 PM.
axilant is offline  
Old 07-24-2005, 01:14 AM   #2 (permalink)
Senior Member
 
Porte's Avatar
Join Date: May 2005
Location: I'm right here
Posts: 3,526
Porte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud of
 
Thanks a lot. I was coding from scratch, so I think I can use something like this now.
__________________
WP Theme Developer
Your One-stop for Premium Magazine/CMS WordPress Themes
Deluxe Themes
Porte is offline  
Old 07-26-2005, 07:11 PM   #3 (permalink)
New Member
Join Date: Sep 2003
Posts: 13
unclekyky is an unknown quantity at this point
 
why not just use mysql_real_escape_string() on any data sent to MySQL?
unclekyky is offline  
Old 07-26-2005, 07:35 PM THREAD STARTER               #4 (permalink)
Account Closed
 
axilant's Avatar
Join Date: May 2004
Location: /etc/passwd
Posts: 2,178
axilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to behold
 

Member of the Month July 2005

My reason is so i dont have to rewrite around 500 queries. Unless your willing to do it for free :S
axilant is offline  
Old 07-26-2005, 08:00 PM   #5 (permalink)
New Member
Join Date: Sep 2003
Posts: 13
unclekyky is an unknown quantity at this point
 
i guess thats the beauty of database abstraction.
unclekyky is offline  
Old 07-28-2005, 07:53 AM   #6 (permalink)
Senior Member
 
Porte's Avatar
Join Date: May 2005
Location: I'm right here
Posts: 3,526
Porte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud of
 
mysql_real_escape_string() works very well. And its suggested by the php manual.
__________________
WP Theme Developer
Your One-stop for Premium Magazine/CMS WordPress Themes
Deluxe Themes
Porte is offline  
Old 07-28-2005, 03:20 PM THREAD STARTER               #7 (permalink)
Account Closed
 
axilant's Avatar
Join Date: May 2004
Location: /etc/passwd
Posts: 2,178
axilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to behold
 

Member of the Month July 2005

Originally Posted by Designporte
mysql_real_escape_string() works very well. And its suggested by the php manual.
I have code with over 1200 queries :S So i rather not go back and change em!

Alright.

Heres my NEW mysql class, i decided to go back and rewrite it using my "escape" function

PHP Code:
<?PHP
/*
$hostname = "localhost";
$database = "db";
$username = "root";
$password = "";
$db = new db("$hostname:$database:$username:$password");
 */
class db 
{
    var $_version = "1.0";
    var $_lastupdate = "7/28/2005";
    var $_author = "Cody Selzer <axilant@gmail.com>";
    function db($args)
    {
        $arg = explode(":", $args);
        $this->host = $arg[0];
        $this->database = $arg[1];
????: NamePros.com http://www.namepros.com/showthread.php?t=108827
        $this->username = $arg[2];
        $this->password = $arg[3];
        $this->connect();
        $this->select_db();
        $this->close();
    }

    function connect() {
    global $cls;

    $this->connect = @mysql_connect("$this->host", "$this->username", "$this->password");
        if(!$this->connect) {
        $this->error("Mysql Connection Error","Failed connecting to database server\r\n\r\n" . mysql_error() . "");
        }

    return $this->connect;
    }

    function select_db() {
    global $cls;

    $this->select_db = @mysql_select_db($this->database, $this->connect);
        if(!$this->select_db) {
        $this->error("Mysql Connection Error","Failed selecting to database\r\n\r\n" . mysql_error() . "");
        }

    return $this->select_db;
    }

    function query($query) {
    global $cls;

    $this->result = mysql_query($query);
        if(!$this->result) {
        $this->error("Mysql Query Error","Error Message: Failed executing database query\r\nDate/Time: " . date('Y-m-d H:i:s') . "\r\nQuery: $query\r\nMySQL Error: " . mysql_error() . "");
        }

    $detect1 = preg_replace('/DELETE/siU', 1, $query);
    $detect2 = preg_replace('/UPDATE/siU', 1, $query);
????: NamePros.com http://www.namepros.com/showthread.php?t=108827
    $detect3 = preg_replace('/INSERT/siU', 1, $query);

        if($detect1 == 1 || $detect2 == 1 || $detect2 == 1) {
        $this->affected_rows = mysql_affected_rows();
        $this->affected_rows_total += $this->affected_rows;
        }

    $this->counter++;
    return $this->result;
    }

    function fetch_row($query) {
    $this->result = mysql_query($query);
    $this->row = $this->fetch_array($this->result);

    $this->counter++;
    return $this->row;
    }

    function fetch_array($query) {
    $this->row = @mysql_fetch_array($query);

    return $this->row;
    }

    function num_rows($query) {
    $this->result = mysql_query($query);
    $this->num_rows = mysql_num_rows($this->result);

    $this->counter++;
    return $this->num_rows;
    }
    function escape(&arr)
    {
        foreach($arr as $k => $v)
        {
            if (is_array($v))
            {
                $this->escape($arr[$k]);
            }
            else
            {
                if (function_exists('get_magic_quotes'))
                {
                    if(!get_magic_quotes_gpc())
                    {
                        $arr[$k] = stripslashes($v);
                    }
                }
                $arr[$k] = mysql_real_escape_string($v):
            }
           }
    }
    function count_queries() {
    return $this->counter;
    }

    function affected_rows() {
        if($this->affected_rows_total == '0') {
        $this->affected_rows_total = '0';
        }

    return $this->affected_rows_total; 
    }
    function list_dbs()
    {
        return mysql_list_dbs();
    }
    function tablename($query)
    {
        return mysql_tablename($query);
    }
    function num_fields($query)
    {
        return mysql_num_fields($query);
    }
    function error($title,$msg)
    {
        exit("<h3>$title</h3>$msg");
    }

    function close() {
    register_shutdown_function('mysql_close');
    }
}
?>
when you are going to use a $_POST $_GET, $_COOKIE, $_REQUESt ect...
use:
PHP Code:
$db->escape($_POST); //(replace _POSt with your input array) 

Im updating this in 5-10 minutes. Edit: Updated. (I saw an error.)
Last edited by axilant; 07-28-2005 at 03:40 PM.
axilant is offline  
Old 07-28-2005, 05:39 PM   #8 (permalink)
Senior Member
 
Porte's Avatar
Join Date: May 2005
Location: I'm right here
Posts: 3,526
Porte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud of
 
Very nice, but its very complicated to me only.
I'm not too familiar with classes, well I think I would stick with the input validation at the first place (first post).
__________________
WP Theme Developer
Your One-stop for Premium Magazine/CMS WordPress Themes
Deluxe Themes
Porte is offline  
Old 08-02-2005, 09:38 PM   #9 (permalink)
Account Closed
 
Refrozen's Avatar
Join Date: Jun 2005
Posts: 745
Refrozen will become famous soon enoughRefrozen will become famous soon enough
 
Originally Posted by unclekyky
why not just use mysql_real_escape_string() on any data sent to MySQL?
I wrap that in to my database classes entirely. No need to deal with security, my class does it
Refrozen is offline  
Old 08-09-2005, 08:58 AM   #10 (permalink)
Eating Pie
 
iNod's Avatar
Join Date: Nov 2004
Location: Canada
Posts: 2,272
iNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud ofiNod has much to be proud of
 


Special Olympics AIDS/HIV Cystic Fibrosis Save The Children Baby Health Cystic Fibrosis
One of the other biggest made ways to injection is $select = "SELECT *...";
Than $select = mysql_query($select);

This opens up to a way where the user can just do
index.php?select=DELETE FROM clients WHERE user=bob

Something as easy as that can destory a business.

But yes I reccommand the script above to new programmers. More advanced ones have other ways in which to make sure no sql injections occur.

Regards,
__________________
I feel old.
iNod is offline  
Old 08-09-2005, 10:17 AM   #11 (permalink)
Senior Member
Join Date: May 2003
Posts: 2,187
adam_uk is a jewel in the roughadam_uk is a jewel in the roughadam_uk is a jewel in the rough
 


Breast Cancer
Originally Posted by iNod
One of the other biggest made ways to injection is $select = "SELECT *...";
Than $select = mysql_query($select);

This opens up to a way where the user can just do
index.php?select=DELETE FROM clients WHERE user=bob

Something as easy as that can destory a business.

But yes I reccommand the script above to new programmers. More advanced ones have other ways in which to make sure no sql injections occur.
????: NamePros.com http://www.namepros.com/showthread.php?t=108827

Regards,
to be honest if your retarded enough to pass sql queries through urls you should be shot.
adam_uk is offline  
Old 08-09-2005, 01:20 PM THREAD STARTER               #12 (permalink)
Account Closed
 
axilant's Avatar
Join Date: May 2004
Location: /etc/passwd
Posts: 2,178
axilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to behold
 

Member of the Month July 2005

Originally Posted by adam_uk
to be honest if your retarded enough to pass sql queries through urls you should be shot.
Rofl... yea really rofl thats about the funniest thing i ever heard ^^'
????: NamePros.com http://www.namepros.com/showthread.php?t=108827

even if you encrypt them... its still not safe... theres ways to decrypt most things including md5 (takes a while though, visual basic program that will keep running till it finds a match ^^)
axilant is offline  
Old 08-09-2005, 02:04 PM   #13 (permalink)
Senior Member
 
Porte's Avatar
Join Date: May 2005
Location: I'm right here
Posts: 3,526
Porte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud ofPorte has much to be proud of
 
That's weird..I never heard about passing queries through URLs.
__________________
WP Theme Developer
Your One-stop for Premium Magazine/CMS WordPress Themes
Deluxe Themes
Porte is offline  
Old 08-09-2005, 02:09 PM THREAD STARTER               #14 (permalink)
Account Closed
 
axilant's Avatar
Join Date: May 2004
Location: /etc/passwd
Posts: 2,178
axilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to behold
 

Member of the Month July 2005

Originally Posted by Designporte
That's weird..I never heard about passing queries through URLs.

Keep it that way, unless you want to make me a fun place ^^'
axilant is offline  
Old 10-03-2005, 01:34 PM THREAD STARTER               #15 (permalink)
Account Closed
 
axilant's Avatar
Join Date: May 2004
Location: /etc/passwd
Posts: 2,178
axilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to beholdaxilant is a splendid one to behold
 

Member of the Month July 2005

I bring this topic back up because i have made the first post in this thread much better, to explain it a bit better than i have before. Website Security is probably the hardest thing to come by.
axilant is offline  
Closed Thread

« Please Help Me Fix These Browser Errors | Simple Contact Form »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Thread Thread Starter Forum Replies Last Post
M6.Net has the SQL plan for developers looking for space and freedom. priyanka[imported] Web Hosting Offers 1 07-22-2005 02:22 AM
Free cPanel, RvSkins, Fantastico, SQL Hosting! dc1pop Web Hosting Offers 0 06-26-2005 08:29 AM
$ 6.95 Windows Hosting :: MS SQL, ASP.NET, Cold Fusion, Oracle, Multiple domains JodoHost Web Hosting Offers 0 03-27-2005 05:36 AM
Connecting to your SQL Database using Enterprise Manager priyanka[imported] Web Hosting Discussion 1 12-07-2004 04:21 PM
Connecting to your SQL Database using Enterprise Manager priyanka[imported] Webmaster Tutorials 0 12-06-2004 09:58 PM

Liquid Web Smart Servers  
All times are GMT -7. The time now is 01:09 AM.

Contact Us - NamePros.com - Privacy Statement - Top
Managed Web Hosting by Liquid Web
Domain name forum recommended by Domaining.com Powered by: vBulletin® Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.6.0 Ad Management plugin by RedTyger