Page 1 of 1

how to secure mysql class

Posted: Thu Mar 31, 2011 5:22 pm
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!

Re: how to secure mysql class

Posted: Thu Mar 31, 2011 6:53 pm
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"

Re: how to secure mysql class

Posted: Fri Apr 01, 2011 1:43 am
by xexmple
okay thanks,

but the vars are secured ?

Re: how to secure mysql class

Posted: Fri Apr 01, 2011 2:24 am
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";

Re: how to secure mysql class

Posted: Fri Apr 01, 2011 3:25 am
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.

Re: how to secure mysql class

Posted: Fri Apr 01, 2011 4:34 am
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.