[Share] Protecting Sensitive Data

[spamman]

How do you go about protecting sensitive/confidential data in a database? such as passwords TB_USER?
Well, I took the liberty of being the first person to do this, and I know that for a fact, doesn't matter, the information is publicly available to do this. The first thing you need to do is decide on a method do protect your data. I chose hashing because of it's obvious simplicity. I then chose the md5 hash method. MD5 is quite secure, but nothing is invincible. If you want to use the best you can, I would suggest the blowfish encryption. That shit is nice, but quite overkill for something as simple as a ko db. chances are, nobody will even get into your db, and if they do, they will really have to have some time on their hands to start cracking md5 hashes. Of course, weak passwords are still vulnerable, Do not allow '123321','123456','abc123','123abc'. These passwords are easily guessed, and make the accounts vulnerable.Another thing you should do is make sure the users password in no way refers to the account id. For example, you don't want a user "jonny" with the password "jonny' or "jonny123" nothing like that, it can be guessed lightning quick, and if a password can be guessed, what purpose does it serve?

Ok, so now you've set some password restrictions up, now it's time to start protecting your data!
I've found the best way to do this is to first take your web server offline so no new accounts are created while we do this. Then open tb_user and change the datatype of strpasswd to varchar(400). this is to allow change in size of password. Then, you are going to run "update tb_user set strpasswd = hashbytes('md5',strpasswd)

That query will md5 hash all the passwords in your tb_user. Now, you may be wondering how do we check the password if it's now hashed? Good question, let's start with the simple.

Firstly you will need to allow users to login. But before we can do that, you need to open your tb_user and change the datatype of strpasswd from varchar(400) to varbinary(50). save that table again, and now if you open it you'll see <binary data> as the strpasswd. This is cool, but if you go "select * from tb_user" in query analyzer you will see that it still shows you the varbinary data. This is good, and we can use this.

Open your account_login procedure. This is the most important step.

DECLARE @pwd varbinary(50), @ccc2 int, @passwordh varbinary(50)
SET @pwd = null
select @pwd = strpasswd from tb_user where straccountid = @accountid
SELECT @ccc2 = count(*) from tb_user where straccountid = @accountid and strpasswd = HashBytes('MD5', @password)
set @passwordh = hashbytes('MD5',@password)

IF @pwd IS null
BEGIN
	
             SET @nRet = 4
	RETURN
END
ELSE IF @pwd <> @passwordh
BEGIN
	
             SET @nRet = 3
	RETURN
END

If you have any of those variables defined anywhere else, this will error, make sure you only define them once as what i just specified. WALA, your passwords in your database are now hashed and hacker friendly.of course, this does not and will not and cannot protect against a hacker Updating passwords (that's why i would suggest using an oddball hash), deleting rows from the table, but if the db is actually taken, they won't be able to do much with the tb_user table.

[Advocate]

jon will this work like i want it using your old login script combine?

ALTER PROCEDURE ACCOUNT_LOGIN
@AccountID varchar(21),
@Password varbinary (50),
@nRet smallint OUTPUT
AS
DECLARE @Nation tinyint, @CharNum smallint, @Authority tinyint, @char1 char(21), @char2 char(21), @char3 char(21), @auth tinyint, @active tinyint
SET @Nation = 0
SET @CharNum = 0
SET @Authority = 1
DECLARE @pwd varbinary(50), @ccc2 int, @passwordh varbinary(50)
SET @pwd = null
select @pwd = strpasswd from tb_user where straccountid = @accountid
SELECT @ccc2 = count(*) from tb_user where straccountid = @accountid and strpasswd = HashBytes('MD5', @password)
set @passwordh = hashbytes('MD5',@password)

IF @pwd IS null
BEGIN
	
             SET @nRet = 4
	RETURN
END
ELSE IF @pwd <> @passwordh
BEGIN
	
             SET @nRet = 3
	RETURN
END
SELECT @Authority = strAuthority FROM [dbo].[TB_USER] WHERE strAccountID = @AccountID
IF @Authority = 255
BEGIN
SET @nRet = 4
RETURN
END
Select @char1 = strcharid1, @char2 = strcharid2, @char3 = strcharid3 FROM [dbo].[account_char] where straccountid = @Accountid
Select @auth = authority FROM [dbo].[userdata] where struserid = @char1
IF @auth = 255
BEGIN
--SET @nRet = 0
SET @nRet = 4
RETURN
END
Select @auth = authority FROM [dbo].[userdata] where struserid = @char2
IF @auth = 255
BEGIN
--SET @nRet =0
SET @nRet = 4
RETURN
END
Select @auth = authority FROM [dbo].[userdata] where struserid = @char3
IF @auth = 255
BEGIN
--SET @nRet = 0
SET @nRet = 4
RETURN
END

BEGIN TRAN

COMMIT TRAN
Insert into mylogintable (straccountid) values (@accountid)--NEW needed for FIX
SELECT @Nation = bNation, @CharNum = bCharNum FROM [dbo].ACCOUNT_CHAR WHERE strAccountID = @AccountID
DELETE FROM LOGIN_CHECK WHERE strAccountID=@AccountID
INSERT INTO [dbo].LOGIN_CHECK VALUES (@AccountID, getDate())


IF @@ROWCOUNT = 0
BEGIN
SET @nRet = 1
RETURN
END
IF @CharNum = 0
BEGIN
SET @nRet = 1
RETURN
END
ELSE
BEGIN
--SET @nRet = @Nation+1
SET @nRet = 1
RETURN
END
GO

[spamman]

try

ALTER PROCEDURE ACCOUNT_LOGIN
@AccountID varchar(21),
@Password varchar (50),
@nRet smallint OUTPUT
AS
DECLARE @Nation tinyint, @CharNum smallint, @Authority tinyint, @char1 char(21), @char2 char(21), @char3 char(21), @auth tinyint, @active tinyint
SET @Nation = 0
SET @CharNum = 0
SET @Authority = 1
DECLARE @pwd varbinary(50), @ccc2 int, @passwordh varbinary(50)
SET @pwd = null
select @pwd = strpasswd from tb_user where straccountid = @accountid
SELECT @ccc2 = count(*) from tb_user where straccountid = @accountid and strpasswd = HashBytes('MD5', @password)
set @passwordh = hashbytes('MD5',@password)

IF @pwd IS null
BEGIN
	
             SET @nRet = 4
	RETURN
END
ELSE IF @pwd <> @passwordh
BEGIN
	
             SET @nRet = 3
	RETURN
END
SELECT @Authority = strAuthority FROM [dbo].[TB_USER] WHERE strAccountID = @AccountID
IF @Authority = 255
BEGIN
SET @nRet = 4
RETURN
END
Select @char1 = strcharid1, @char2 = strcharid2, @char3 = strcharid3 FROM [dbo].[account_char] where straccountid = @Accountid
Select @auth = authority FROM [dbo].[userdata] where struserid = @char1
IF @auth = 255
BEGIN
--SET @nRet = 0
SET @nRet = 4
RETURN
END
Select @auth = authority FROM [dbo].[userdata] where struserid = @char2
IF @auth = 255
BEGIN
--SET @nRet =0
SET @nRet = 4
RETURN
END
Select @auth = authority FROM [dbo].[userdata] where struserid = @char3
IF @auth = 255
BEGIN
--SET @nRet = 0
SET @nRet = 4
RETURN
END

BEGIN TRAN

COMMIT TRAN
Insert into mylogintable (straccountid) values (@accountid)--NEW needed for FIX
SELECT @Nation = bNation, @CharNum = bCharNum FROM [dbo].ACCOUNT_CHAR WHERE strAccountID = @AccountID
DELETE FROM LOGIN_CHECK WHERE strAccountID=@AccountID
INSERT INTO [dbo].LOGIN_CHECK VALUES (@AccountID, getDate())


IF @@ROWCOUNT = 0
BEGIN
SET @nRet = 1
RETURN
END
IF @CharNum = 0
BEGIN
SET @nRet = 1
RETURN
END
ELSE
BEGIN
--SET @nRet = @Nation+1
SET @nRet = 1
RETURN
END
GO

[Advocate]

since i can't get you to respond about this matter on msn , guess i will post here have done everything to registration script , login script , stored procedures and tb_user . but when i try to login panel now i get Login Error
Invalid Username or password

mind telling me why is not reading password ?

<?php

include('./anti_inject.php');
include('./settings.php');

$login = $_POST['user'];
$pass = $_POST['pw'];

$login = test($login);
$pass = test($pass);

$msconnect=odbc_connect("$dbname","$dbuser","$dbpass");
$msquery="SELECT COUNT(strACcountID) FROM [ACCOUNT].dbo.tb_user WHERE strACcountID = '$login' AND strPasswd = hashbytes ('md5','$pass')";
$msresults=odbc_exec($msconnect,$msquery) or die("error");
odbc_fetch_row($msresults);

if (odbc_result($msresults,1) > 0) {
	$msquery="SELECT COUNT(us.strUserID) FROM USERDATA us, ACCOUNT_CHAR ac WHERE (ac.strCharID1 = us.strUserID OR ac.strCharID2 = us.strUserID OR ac.strCharID3 = us.strUserID) AND ac.strACcountID = '$login' AND us.Authority = '0'";
	$msresults=odbc_exec($msconnect,$msquery) or die("error");
	odbc_fetch_row($msresults);
	if (odbc_result($msresults,1) > 0) {
		$_SESSION['sesuser'] = $_POST['user'];
		$_SESSION['sespw'] = $_POST['pw'];
		$_SESSION['sesadmin'] = "1";
		header("Location: index.php");
	} else {
		$_SESSION['sesuser'] = $_POST['user'];
		$_SESSION['sespw'] = $_POST['pw'];
		$_SESSION['sesadmin'] = "0";
		header("Location: index.php");
	}
} else {
	header("Location: index.php?act=logerror");
}










/*
$_SESSION['user'] = $_POST['user'];
$_SESSION['pw'] = $_POST['pw'];

$head = 'Location: ./user/index.php
header($head);*/
?>

[grzequ]

Advocate, you're saving a non hashed password to the session data:

 $_SESSION['sespw'] = $_POST['pw']; 
  

And I guess a totally different script is comparing these values later on. So it should rather look like:

 $_SESSION['sespw'] = md5($_POST['pw']); 
  

I haven't done any php programming in a long time, so I might be wrong, or there might be a few more things you got to change :)

Basically, every database call should compare the database value with md5 hashed user input.

[SexyReplay]

you are wrong.

[grzequ]

Alright, but could you point me at which point I'm wrong? And if you know that I'm wrong then you should know how it's supposed to like. Won't you mind sharing?

Anyway, storing unhashed pass***** in session data is not safer than storing them in the database :)

[SexyReplay]

just what you said lol, you dont need to do "$_SESSION['sespw'] = md5($_POST['pw']);" because the "select strpasswd = hashbytes('md5','$password') from tb_User where straccountid = $account is enough, what it does it get the pw and hash it then select the passwd nd see if it match with the login, I already fixed your login.php advo >< i tested it yesterday nd worked for me perfectly so i could be at least 99% sure its not the login.php unless u edited it :x

[grzequ]

ah, i get it now. So it actually is the same script checking the session data later on.

But still:p storing unhashed pass***** in sessions isn't safer than storing them in the database. Sessions are serverside after all :)

[spamman]

what we're aiming for here is simply protecting pass***** in our databases. YOu can go ahead and protect them in sessions if you want, but not many people will be able to steal those. You are wrong when you try to md5($variable); instead you should just change the variable in the login procedure in the count(*) statement. to hashbytes('md5','$pass')

Advocate, please post the sql error.

[IceMan1300]

This has actually been in place on Caffeinated KO for about 3 months now, but I'm not complaining. :P

We use a function with a direct call to a custom DLL with only one purpose - MD5 hash generation.
Simply: [dbo].md5(@strPasswd)

[HellFire]

thanks , i got login working. just the register page is bitching

        $msquery="INSERT INTO tb_user(strACcountID, strPasswd, strSocNo, idays) VALUES ('$login','md5($_POST['pw'])','$id','6') ";

doesnt work at all, or it has sth to do with confirm password also?

    $pw = trim($_POST['pw']);
    $c_pw = trim($_POST['c_pw']);

how would i combine the hasbytes md5 with trim();?

what would be better trim(); or htmlentities();

thanks for your help

[BlaDe2qj]

Also note that will stop injection in the password field atleast heh.
I'd salt the hashes too, for ex md5($password . "BlaDe's sensible");
do the same when you compare it this will stop brute force attacks. If you salt it to $username + "String", or any unique data associated to the password (For example unix timestamp is a reliable one) that's even safer.
HellFire, you're sending literal strings not variables in your query.

[HellFire]

update i tryed the following:

<?php
include('md5.php');
// Skin Settings
$title = $name." ".$lang[15];

// Registration Start
if ($_GET['do'] == 'createaccount') {
    if ($_SESSION['registerOk'] != "canRegister") {
        echo "<script>self.location = 'index.php?act=registration'</script>";
    } else {
        $_SESSION['registerOk'] = "";
    }
    error_reporting(E_ALL ^ E_NOTICE);
    $login = trim($_POST['user']);
    $pw = trim($_POST['pw']);
    $id = trim($_POST['id']);
    $c_pw = trim($_POST['c_pw']);
    $c_id = trim($_POST['c_id']);

    $pwh = passConvert($pw);
      $pwhc = passConvert($c_pw);

    $login = test($login);
    $pw = test($pw);
    $id = test($id);
    $c_pw = test($c_pw);
    $c_id = test($c_id);
    
    
    if ($pw != $c_pw && $id != $c_id) {
        parth1tr();
        parth1(560,4,1);
        echo $lang[15];
        parth2();
        parth2tr();
        part1tr();
        part1(560,4,1,1);
        echo $lang[20] . "<br><a href='$PHP_SELF?act=registration'>Register Again</a>";
        part2();
        part2tr();
        die();
    }
    if ($pw != $c_pw) {
        parth1tr();
        parth1(560,4,1);
        echo $lang[21];
        parth2();
        parth2tr();
        part1tr();
        part1(560,4,1,1);
        echo $lang[20] . "<br><a href='$PHP_SELF?act=registration'>Register Again</a>";
        part2();
        part2tr();
        die();
    }
    if ($id != $c_id) {
        parth1tr();
        parth1(560,4,1);
        echo $lang[22];
        parth2();
        parth2tr();
        part1tr();
        part1(560,4,1,1);
        echo $lang[20] . "<br><a href='$PHP_SELF?act=registration'>Register Again</a>";
        part2();
        part2tr();
        die();
    }
    if (!empty($pw) && strlen($pw) < 4) {
        parth1tr();
        parth1(560,4,1);
        echo $lang[23];
        parth2();
        parth2tr();
        part1tr();
        part1(560,4,1,1);
        echo $lang[20] . "<br><a href='$PHP_SELF?act=registration'>Register Again</a>";
        part2();
        part2tr();
        die();
    }
    if (!empty($id) && !ereg("[0-9]", $id)) {
        parth1tr();
        parth1(560,4,1);
        echo $lang[24];
        parth2();
        parth2tr();
        part1tr();
        part1(560,4,1,1);
        echo $lang[20] . "<br><a href='$PHP_SELF?act=registration'>Register Again</a>";
        part2();
        part2tr();
        die();
    }
    if (!empty($id) && strlen($id) < 4) {
        parth1tr();
        parth1(560,4,1);
        echo $lang[25];
        parth2();
        parth2tr();
        part1tr();
        part1(560,4,1,1);
        echo $lang[20]  . "<br><a href='$PHP_SELF?act=registration'>Register Again</a>";
        part2();
        part2tr();
        die();
    }

    if (empty($login) || empty($pw) || empty($id) || empty($c_pw) || empty($c_id)) {
        die($lang[26]);
    }

    if (!empty($login) && !empty($pw) && !empty($id) && !empty($c_pw) && !empty($c_id)) {
        $msconnect=odbc_connect("$dbname","$dbuser","$dbpass");
        $msteste="SELECT COUNT(strACcountID) FROM tb_user WHERE strAccountID = '$login'";
        $msresul=odbc_exec($msconnect,$msteste) or die($lang[27]);
        odbc_fetch_row($msresul);
        if (odbc_result($msresul,1) > 0) {
            parth1tr();
            parth1(560,4,1);
            echo $lang[15];
            parth2();
            parth2tr();
            part1tr();
            part1(560,4,1,1);
            echo "This Account in using try other.<br><a href='$PHP_SELF?act=registration'>Register Again</a><table height='328'><tr><td height='328'>&nbsp;</td></tr></table>";
            part2();
            part2tr();
        } else {
            $msquery="INSERT INTO tb_user(strACcountID, strPasswd, strSocNo, idays) VALUES ('$login','$pwh','$id','6') ";
            $msresults=odbc_exec($msconnect,$msquery) or die($lang[27]);

            parth1tr();
            parth1(560,4,1);
            echo $lang[15];
            parth2();
            parth2tr();
            part1tr();
            part1(560,4,1,1);
            echo $lang[28]."<p>".$lang[1].": ".$login."<br>".$lang[16].": ".$pw."<br>".$lang[18].": ".$id;
            part2();
            part2tr();
        }
    }
} else {
        
        $_SESSION['registerOk'] = "canRegister";
        $register = '<center><FORM method=post name=regform action=index.php?act=registration&do=createaccount>
        <table width="350">
        <tr>
        <td>
        <center></center></td>
        </tr>
        <tr></tr>
        <tr>
        <td align="right">'.$lang[1].':&nbsp;</td>
        <td width="200"><input type="text" class="input" name="user" value="" maxlength="12"  size="10"></td>
        <tr><td>&nbsp;</td><td></td></tr>
        <tr>
        <td align="right">'.$lang[16].':&nbsp;</td>
        <td><input type="password" class="input" name="pw" value="" maxlength="12"  size="10"></td>
        </tr>
        <tr>
        <td align="right">'.$lang[17].' '.$lang[16].':&nbsp;</td>
        <td><input type="password" class="input" name="c_pw" value="" maxlength="12"  size="10"></td>
        </tr>
        <tr><td>&nbsp;</td><td></td></tr>
        <tr>
        <td align="right">ID:</td>
        <td><input type="password" class="input" name="id" value="" maxlength="4" size="10"></td>
        </tr>
        <tr>
        <td align="right">'.$lang[17].' ID:</td>
        <td><input type="password" class="input" name="c_id" value="" maxlength="4" size="10"> </td>
        </tr>
        </table>
        <input class="input" type="submit" value="Register">
        </form>
        </center>';

        parth1tr();
        parth1('25%', 1, 1);
        echo $lang[15];
        parth2();
        parth2tr();
        part1tr();
        part1('25%', 1, 1, 'left');
        echo $register;
        echo '<table height="328"><tr><td height="328">&nbsp;</td></tr></table>';
        part2();
        part2tr();
}
?>

md5.php

<?php


function passConvert($password)
{
$encar = array('!'=>'95', '"'=>'88', '#'=>'9D', '$'=>'4C', '%'=>'F2', '&'=>'3E', '\''=>'BB', '('=>'C0', ')'=>'7F', '*'=>'18', '+'=>'70', ','=>'A6', '-'=>'E2', '.'=>'EC', '/'=>'77',
'0'=>'2C', '1'=>'3A', '2'=>'4A', '3'=>'91', '4'=>'5D', '5'=>'7A', '6'=>'29', '7'=>'BC', '8'=>'6E', '9'=>'D4', ':'=>'40', ';'=>'17', '<'=>'2E', '='=>'CB', '>'=>'72', '?'=>'9C',
'@'=>'A1', 'A'=>'FF', 'B'=>'F3', 'C'=>'F8', 'D'=>'9B', 'E'=>'50', 'F'=>'51', 'G'=>'6D', 'H'=>'E9', 'I'=>'9A', 'J'=>'B8', 'K'=>'84', 'L'=>'A8', 'M'=>'14', 'N'=>'38', 'O'=>'CE',
'P'=>'92', 'Q'=>'5C', 'R'=>'F5', 'S'=>'EE', 'T'=>'B3', 'U'=>'89', 'V'=>'7B', 'W'=>'A2', 'X'=>'AD', 'Y'=>'71', 'Z'=>'E3', '['=>'D5', '\\'=>'BF', ']'=>'53', '^'=>'28', '_'=>'44',
'`'=>'33', 'a'=>'48', 'b'=>'DB', 'c'=>'FC', 'd'=>'09', 'e'=>'1F', 'f'=>'94', 'g'=>'12', 'h'=>'73', 'i'=>'37', 'j'=>'82', 'k'=>'81', 'l'=>'39', 'm'=>'C2', 'n'=>'8D', 'o'=>'7D',
'p'=>'08', 'q'=>'4F', 'r'=>'B0', 's'=>'FE', 't'=>'79', 'u'=>'0B', 'v'=>'D6', 'w'=>'23', 'x'=>'7C', 'y'=>'4B', 'z'=>'8E', '{'=>'06', '|'=>'5A', '}'=>'CC', '~'=>'62');

$newpass = "0x";
for ($i = 0; $i < strlen($password); $i++)
{
$newpass .= $encar[$password[$i]];
}

return $newpass;
}


?>

Warning:  odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query., SQL state 37000 in SQLExecDirect in E:\AC Web Ultimate Repack\Server\htdocs\registration.php on line 131
Please Choose another Username!

[spamman]

hellfire, in the guide that i explained this in, I used sql to hash the passwords. not php. That way, when you check password upon login to the server, sql is once again hashing, and more reliable i think if you use the same thing for both instances.