how to secure mysql class

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
xexmple
Forum Newbie
Posts: 6
Joined: Thu Mar 31, 2011 5:06 pm

how to secure mysql class

Post by xexmple »

hey guys,

i'm new here and in php so be easy on me.

i made class to handle the database and i used in mysql.

i have on function that inser array to the database and before it's insert i secured the inputs.

the function:

Code: Select all

function insertArray($tableName, $arrayData)
   {
       if ( ! empty($arrayData) && !empty($tableName) )
       {
           $cols = '(';
           $values = '(';
           foreach ($arrayData as $key=>$value)
           {    
                   $cols .= "`$key`,";

                   $value = htmlspecialchars(mysql_real_escape_string($value));
                   $values .= "'$value',";
            }
            $cols = rtrim($cols, ',').')';
            $values = rtrim($values, ',').')';

            $sql = mysql_query("INSERT INTO $tableName $cols VALUES $values");
       }
       else
       {
           die('There was error in the insertArray!');
       }
   }

code for insert query

Code: Select all

$nickname = $_POST['nickname'];
$email = $_POST['email'];
$Password = md($_POST['nickname']);
$registerForm = array(
    "nickname" => "$nickname",
    "email" => "$email",
    "password" => "$password"
);
$Db->insertArray('members', $registerForm);
i'm sorry about my eng, and the typo.

thanks a lot, and have great day!
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: how to secure mysql class

Post by McInfo »

Did you forget to ask a question, or are you just sharing your code?

These function calls are in reverse order.

Code: Select all

htmlspecialchars(mysql_real_escape_string($value))
You should check whether the "nickname" and "email" keys exist in the $_POST array before assuming that they are there. Otherwise, these copy variables are unnecessary and you should use the $_POST elements instead.

Code: Select all

$nickname = $_POST['nickname'];
$email = $_POST['email'];
Did you mean to call the md5() function?

Code: Select all

$Password = md($_POST['nickname']);
Request variables are already strings; so there is no need to quote them. Anyway, as I mentioned before, use $_POST['nickname'] here if $nickname is just a copy of it.

Code: Select all

"nickname" => "$nickname"
The variable used here is not the same as the similarly-named variable assigned before. Variables are case-sensitive.

Code: Select all

"password" => "$password"
xexmple
Forum Newbie
Posts: 6
Joined: Thu Mar 31, 2011 5:06 pm

Re: how to secure mysql class

Post by xexmple »

okay thanks,

but the vars are secured ?
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: how to secure mysql class

Post by Mordred »

The one thing I disagree with is that these are in reverse order:

Code: Select all

htmlspecialchars(mysql_real_escape_string($value))
While technically true, this has other problems:

Code: Select all

mysql_real_escape_string(htmlspecialchars($value))
1. It assumes that $value is intended for HTML display only. What if you read it from one SQL table and insert it in another?
2. htmlspecialchars() is not called correctly, you should always use ENT_QUOTES and specify the correct encoding.
3. Escaping for HTML should be done right before you output it, not in any intermediate step.

So the correct code would be simply:

Code: Select all

$value = mysql_real_escape_string($value);
Later, when you want to display it:

Code: Select all

//pull $value from the database
$value = htmlspecialchars($value, ENT_QUOTES, "utf-8"); //assuming you use utf-8
echo "Here's my value, safe from XSS: $value";
xexmple
Forum Newbie
Posts: 6
Joined: Thu Mar 31, 2011 5:06 pm

Re: how to secure mysql class

Post by xexmple »

the insert query not be compromised to sql injection if i remove the htmlspecialchars ?

Code: Select all


$value = mysql_real_escape_string($value);

thanks for the patience to me.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: how to secure mysql class

Post by Mordred »

The way to protect against SQL injection is to use the SQL escape function (mysql_real_escape_string), NOT the HTML escape function (htmlspecialchars).
You can check the article in my sig for a more detailed discussion.
Post Reply