Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 PLEASE help me , willing to pay if needed

Author  Topic 

godlydanny
Starting Member

25 Posts
Posted - 2010-12-07 : 08:53:00
Hello please help me solve this error:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type binary, table 'databasename.dbo.users', column 'passwd'. Use the CONVERT function to run this query., SQL state 37000 in SQLExecDirect in C:\xampplite\htdocs\Register\index.php on line 176

Seems like I need to convert my text password to Binary(16),
but I dont know how please help me or contact me at godlydanny@hotmail.com for further assistant. I am willing to pay if anyone can do this registration system for me.

Thank you for your kind attention
Sincerely,
Danny

<?php


$ipLog='ipLogFile.txt';
$date=date("Y-m-d H:i:s");

$register_globals = (bool) ini_get('register_gobals');

if ($register_globals) {$vis_ip = getenv(REMOTE_ADDR);}
else {$vis_ip = $_SERVER['REMOTE_ADDR'];}



function recordData($vis_ip,$ipLog,$date,$username,$mail)
{

//$filePointer = fopen($ipLog,"a+");
//$logMsg = $date."][".$vis_ip."][".$mail."][".$username."\n";
//fputs($filePointer,$logMsg);
//fclose($filePointer);
}


function checkLog($vis_ip,$ipLog)
{
global $valid; $ip=$vis_ip;
$data=file("$ipLog");
foreach ($data as $record)
{
$subdata=explode("][",$record);
if ($ip == $subdata[1])
{
$valid=0;
break;
}
}
}



//checkLog($vis_ip,$ipLog);




// error reporting
error_reporting(E_ALL);
ini_set('display_errors','on');

// configuration
$config = array(
'db_username' => 'sa', // database username
'db_password' => 'password', // database password
'db_dsn' => 'odbc_users', // system DSN to the database
'template' => 'registration.tpl', // registration template path/filename
'debug' => false, // show SQL errors if true
);

// HTML error
define('UI_ERROR','<span class="error">%s</span>');

// if submitted
if(strtolower($_SERVER['REQUEST_METHOD']) == 'post') {


checkLog($vis_ip,$ipLog);

$username = $_POST['username'];
$password = $_POST['password'];

$mail=$_POST["email"];
$emailresult=preg_match("/^[^@ ]+@[^@ ]+\.[^@ ]+$/",$mail,$trashed);

$error = array();
// validate username
if(!ctype_alnum($username)) {
$error['username'] = sprintf(UI_ERROR,'Illigal characters in the username');
}
else
if ((StrLen($username) < 1) or (StrLen($username) > 8)) {
$error['username'] = sprintf(UI_ERROR,'Username must be between 1-8 characters');
}
else
if ((StrLen($password) < 1) or (StrLen($password) > 8)) {
$error['password'] = sprintf(UI_ERROR,'Password must be between 1-8 characters');
}
else
if ($valid =="0") {
$error['iplocked'] = sprintf(UI_ERROR,'You can not create more accounts with us');
}
// validate password
else
if(!ctype_alnum($password)) {
$error['password'] = sprintf(UI_ERROR,'Illigal characters in the password');
}
else
if(!$emailresult){
$error['email'] = sprintf(UI_ERROR,'Please enter a valid email');
}



// no errors, continue to username check
if(empty($error)) {
// db connect
$conn = odbc_connect($config['db_dsn'],
$config['db_username'],
$config['db_password']);
// check about account name is taken
$check = "SELECT
[name] FROM [users]
WHERE
[name]='%s'
OR
[name]='%s'
OR
[name]='%s'
OR
[name]='%s'
";
$check = sprintf($check,$username,
strtolower($username),
strtoupper($username),
ucfirst($username)
);

$exec = odbc_exec($conn,$check);
// check for errors
if(!$exec && ($config['debug'] === true)) {
echo odbc_errormsg($conn);
die();
}
// is the account registered?
$data = odbc_fetch_array($exec);
if($data !== false) {
$error['username'] = sprintf(UI_ERROR,'Account already registered,
please choose another name');
} else {
//include 'Password.php';

///WRITE DATA
recordData($vis_ip,$ipLog,$date,$username,$mail);


// encode password
//$password = Password::encode($password);

//$Salt = $username.$password;
//$Salt = md5($Salt);
//$Salt = "0x".$Salt;






// prepare sql
$sql = "INSERT INTO
[users] ([name],[passwd],[Prompt],[answer],[truename],[idnumber],[email],[mobilenumber],[province],[city],[phonenumber],[address],[postalcode],[gender],[birthday],[creatime],[qq],[passwd2])
VALUES
('".$username."','".$password."','','','','','','','','','','','','','','','','')
";











// insert user
$result = odbc_exec($conn,$sql);
if(!$result && ($config['debug'] === true)) {
echo odbc_errormsg($conn);
die();
}


}
}

}




include $config['template'];

?>

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-07 : 09:01:02
The issue is
$sql = "INSERT INTO
[users] ([name],[passwd],[Prompt],[answer],[truename],[idnumber],[email],[mobilenumber],[province],[city],[phonenumber],[address],[postalcode],[gender],[birthday],[creatime],[qq],[passwd2])
VALUES
('".$username."','".$password."','','','','','','','','','','','','','','','','')
";

Create a stored procedure and execute it passing the data as parameters then you can do the convert.

In fact get rid of all the embedded sql and put it into stored procedures. You'll find the system a lot easier to maintain, optimise and diagnose issues.


'".$username."','".$password."'

You seem to be passing the text
".$username."
".$password."

rather than the contents of the columns (again easy to spot if you call an SP - but you could also get this from profiler.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

godlydanny
Starting Member

25 Posts
Posted - 2010-12-07 : 09:10:30
quote:
Originally posted by nigelrivett

The issue is
$sql = "INSERT INTO
[users] ([name],[passwd],[Prompt],[answer],[truename],[idnumber],[email],[mobilenumber],[province],[city],[phonenumber],[address],[postalcode],[gender],[birthday],[creatime],[qq],[passwd2])
VALUES
('".$username."','".$password."','','','','','','','','','','','','','','','','')
";

Create a stored procedure and execute it passing the data as parameters then you can do the convert.

In fact get rid of all the embedded sql and put it into stored procedures. You'll find the system a lot easier to maintain, optimise and diagnose issues.


'".$username."','".$password."'

You seem to be passing the text
".$username."
".$password."

rather than the contents of the columns (again easy to spot if you call an SP - but you could also get this from profiler.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Thank you Nigel for your reply,
Could you please kindly show a demo of what you mean?
I am really new to MSSQL, thanks in advanced.

Best regards,
Danny
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-07 : 09:21:35
The issue here I think is that you are passing the name of the variable rather than it's contents so the isue is the interface - an SP inteface would mean you would have control of the dattypes and it would be easy to trace what is happening.

You could have an SP
create s_AddUser
@name varchar(100) ,
@passwd varchar(100) ,
@Prompt varchar(100)
...

as
begin try
if exists (select * from Users where name = @name)
begin
-- gets trapped by catch block
raiserror ('user %s already exists', 16, -1, @name
end
insert users (name, passwd, Prompt)
select @name, convert(binary(32),@passwd), Prompt
end try
begin catch
-- insert error table and raise error
end catch
go

You would add as much logic to an sp as you can so that a single database call executes as much as possible - expecially try not to put any transaction handling in the client.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-07 : 09:29:04
Duplicate
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153889

Please continue on other thread.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2010-12-07 : 09:37:08
What a moron!! Posting the same exact thing in 3 different forums on the same site...grrrrrrrrrrrrrrrrrrrr!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

godlydanny
Starting Member

25 Posts
Posted - 2010-12-07 : 09:40:55
quote:
Originally posted by Lumbago

What a moron!! Posting the same exact thing in 3 different forums on the same site...grrrrrrrrrrrrrrrrrrrr!

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com


Sorry , I am in a rush to finish this script that is why i posted in 3 sections. Just to get attention from more professionals like you guys.

Sincerely,
Danny
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site