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 2000 Forums
 Transact-SQL (2000)
 PLEASE help me , willing to pay if needed

Author  Topic 

godlydanny
Starting Member

25 Posts
Posted - 2010-12-07 : 08:31:38
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'];

?>

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2010-12-07 : 09:09:35
I don't know anything aout php but why are these lines commented out? -> 
//$password = Password::encode($password);

//$Salt = $username.$password;
//$Salt = md5($Salt);
//$Salt = "0x".$Salt;
Seems to me like the hashing and the converting to binary is commented out...try to remove the // and see what happens

- 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:12:53
quote:
Originally posted by Lumbago

I don't know anything aout php but why are these lines commented out? -> 
//$password = Password::encode($password);

//$Salt = $username.$password;
//$Salt = md5($Salt);
//$Salt = "0x".$Salt;
Seems to me like the hashing and the converting to binary is commented out...try to remove the // and see what happens

- Lumbago

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


Hello, thank you for your reply.
I have resolved that problem by removing the ' ' beside my ".$password," .

Now i am getting this error:
my username i inserted is called "testing"
than this error poped out

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]The name 'testing' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted., SQL state 37000 in SQLExecDirect in C:\xampplite\htdocs\Register\index.php on line 176


Best regards,
Danny
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2010-12-07 : 09:31:47
Well, the first rule of debugging is to see what is actually being sent to the database. Can you print the value of $sql to the screen immediately before the "// insert user"-comment and post its contents here? Seeing the actual sql query that is sent to the database usually sheds more light on what the problem is.

- 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:38:06
quote:
Originally posted by Lumbago

Well, the first rule of debugging is to see what is actually being sent to the database. Can you print the value of $sql to the screen immediately before the "// insert user"-comment and post its contents here? Seeing the actual sql query that is sent to the database usually sheds more light on what the problem is.

- Lumbago

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



Here is your requested Print,

INSERT INTO [users] ([name],[passwd],[Prompt],[answer],[truename],[idnumber],[email],[mobilenumber],[province],[city],[phonenumber],[address],[postalcode],[gender],[birthday],[creatime],[qq],[passwd2]) VALUES (dannyuser,dannypass,'','','','','','','','','','','','','','','','')
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2010-12-07 : 09:43:14
Well, there you go. To be able to insert text in to sql server it must be surrounded by single quotes.

And another thing: Cross posting in to 3 forums on the same site like you did will make you really unpopular really fast. One version of the same question will be quite sufficient thank you!

- 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:53:11
quote:
Originally posted by Lumbago

Well, there you go. To be able to insert text in to sql server it must be surrounded by single quotes.

And another thing: Cross posting in to 3 forums on the same site like you did will make you really unpopular really fast. One version of the same question will be quite sufficient thank you!

- Lumbago

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



Alrite thank you for your hint regarding about Cross posting, but could you please demo what I should type in please.
Thank you

Regards,
Danny
Go to Top of Page

godlydanny
Starting Member

25 Posts
Posted - 2010-12-07 : 09:57:18
// 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."','','','','','','','','','','','','','','','','')
";


print $sql;



Outcome :
INSERT INTO [users] ([name],[passwd],[Prompt],[answer],[truename],[idnumber],[email],[mobilenumber],[province],[city],[phonenumber],[address],[postalcode],[gender],[birthday],[creatime],[qq],[passwd2]) VALUES ('testuse','testpas','','','','','','','','','','','','','','','','')

now is correct but than it telling me to CONVERT , from the other post some guy told me to do store procedure , will you say so too?
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-07 : 10:07:36
your password is 'testpas' but from the previous error the datatype needs to be binary rather than varchar.


==========================================
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 : 10:13:54
quote:
Originally posted by nigelrivett

your password is 'testpas' but from the previous error the datatype needs to be binary rather than varchar.


==========================================
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.


Yes Nigel ,
so how could I convert it, could you please give me the code ?

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2010-12-07 : 10:14:40
As I posted before...
Keep single quote around username but not around password
VALUES ('".$username."',".$password.",'','','','','','','','','','','','','','','','')";

And make sure the $password variable holds a value that starts with 0x.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

godlydanny
Starting Member

25 Posts
Posted - 2010-12-07 : 11:15:05
Hello to all who helped me,
here is my finalization .
// encode password
$Salt = $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."',".$Salt.",'','','','','','','','','','','','','','','',".$Salt.")
";


print $sql;




Print Outcome:
INSERT INTO [users] ([name],[passwd],[Prompt],[answer],[truename],[idnumber],[email],[mobilenumber],[province],[city],[phonenumber],[address],[postalcode],[gender],[birthday],[creatime],[qq],[passwd2]) VALUES ('testing',0x098f6bcd4621d373cade4e832627b4f6,'','','','','','','','','','','','','','','',0x098f6bcd4621d373cade4e832627b4f6)

Everything looks good "BUT",
got small error:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'ID', table 'c1o2n3dorheroes.dbo.users'; column does not allow nulls. INSERT fails., SQL state 23000 in SQLExecDirect in C:\xampplite\htdocs\Register\index.php on line 176


Information:
My [users]table is like this
I thought the ID has a "key icon" beside on it means auto increment? doesnt it?

[ID]
[name]
[passwd]
[Prompt]
[answer]
[truename]
[idnumber]
[email]
[mobilenumber]
[province]
[city]
[phonenumber]
[address]
[postalcode]
[gender]
[birthday]
[creatime]
[qq]
[passwd2]
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-07 : 11:21:02
Possibly - more likely just a primary key - have a look at the create script for the table (right click on the table in management studio, script table as create to new query window.
That'll show whether it's an identity or not.

If it is then your insert statement is including it in the column list and trying to insert null. If that is the case I would expect a different error.
More likely it is not and you ned to generate a value for it or change it to identity.

==========================================
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 : 11:30:24
Hi Nigel , thank you for helping .
-----------------------------------------------
here is the result, in the query window
USE [c1o2n3dorheroes]
GO
/****** Object: Table [dbo].[users] Script Date: 12/07/2010 11:27:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
[ID] [int] NOT NULL,
[name] [varchar](32) COLLATE Chinese_PRC_CI_AS NOT NULL,
[passwd] [binary](16) NOT NULL,
[Prompt] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[answer] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[truename] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[idnumber] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[email] [varchar](64) COLLATE Chinese_PRC_CI_AS NULL,
[mobilenumber] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[province] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[city] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[phonenumber] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[address] [varchar](64) COLLATE Chinese_PRC_CI_AS NULL,
[postalcode] [varchar](8) COLLATE Chinese_PRC_CI_AS NULL,
[gender] [int] NULL,
[birthday] [datetime] NULL,
[creatime] [datetime] NOT NULL,
[qq] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
[passwd2] [binary](16) NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
---------------------------------------

Here is the "PK_users" key in query window:
USE [c1o2n3dorheroes]
GO
/****** Object: Index [PK_users] Script Date: 12/07/2010 11:29:22 ******/
ALTER TABLE [dbo].[users] ADD CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]


What should i do now please advise me with your experience?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-12-07 : 11:30:27
"$Salt = $password;
$Salt = md5($Salt);
$Salt = "0x".$Salt;"

This isn't SALT is it? its just HASH surely - and as such open to dictionary attack?

"$sql = "INSERT INTO
[users] ([name],...)
VALUES
('".$username."',...)"

Risk of SQL Injection attack? (or is it the DOT pre/suffix in PHP that solves? Not something I am familiar with)

Why inserting blank strings into other "Contact details" fields? How will you tell the difference between a field that the user entered a blank (deliberately) and where they have not even been asked for any data?

Some reason for storing the password twice? (in [passwd] and [passwd2] columns)

"I thought the ID has a "key icon" beside on it means auto increment? doesnt it?"

No

"[name] [varchar](32) COLLATE Chinese_PRC_CI_AS NOT NULL,"

Do you need to store Chinese characters? If so you can't in VARCHAR, if not then using a Chinese COLLATE is probably going to be trouble.
Go to Top of Page

godlydanny
Starting Member

25 Posts
Posted - 2010-12-07 : 11:40:22
Hi ,
I don't know why in Chinese COLLATE , but the system works like that.

so what are the conclusions? I am really tired I been up over 28 hours trying to script this thing . Please help =(
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-07 : 12:02:54
ok ID is not an identity.
You either need to change it to be one or provide a value (or let it allow nulls or give it a default).
I take it the able is empty at the moment (otherwise it's a different problem)

How was the table created - did you create it yourself or copy it and can you change the structure?
You can just change the create script to be

[ID] [int] identity NOT NULL,
drop the table and run the script.

Might be safer though to do this from management studio
Right click on the table, design
Change the column to be an identity
generate the change script - this will drop an recreate the table and all asociated indexes, keys etc.
Run the script.


Also check the collation of the system databases (master, tempdb)
If these are not the same as your database or columns in your tables then it will probably cause problems later.
Actually dealing with chinese code pages always causes problems.
==========================================
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 : 12:17:27
Those ID are very important as they identifies what my character database to use.

So please provide me a script to make those ID

They must alternate "14" , "16" as it increases
example

16

30 (add 14)

46 (add 16)

60 (add 14)

76 (add 16)

Thank you very much if you can do this for me Nigel .
Sincerely,
Danny
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-12-07 : 12:31:51
Do you have an ID for the user at the time you enter this routine? (i.e. the ID has already been decided by some earlier process)

If so then use that for the ID in the INSERT statement.

If not then, as Nigel says, you are probably better off to let SQL assign that for you by changing the Property on the ID column to IDENTITY

If that's the case are you going to need to get it back in this routine? How will everything else know what ID has been allocated for this user? (You could get around that by forcing the user to Login using the credentials for the record you have just created, but it is generally "kinder" to silently set the user to "logged-in" after storing their credentials).

Although I'm only guessing what your routine is supposed to be doing.

Heads up:

I notice you didn't respond to my other points.

1) If you don't know what SALT and HASH refer to in cryptography then you ought not to be writing a "secure password" routine until you do - you will be liable to be creating an insecure system - and the further risk is that you / your colleagues think it is secure. Its not rocket science (or even degree stuff), you can find it on Wikipedia

2) Ditto with SQL Injection. If you don't know what that is you will be creating a site with security risk. Fixing it will be a nightmare once you have a decent amount of code written. So you need to read up and find out about it before you get to the point of having a huge fix-up job to do (which will be an emergency if you only discover once the site is live because you will have to take the site off-line until it is fixed - otherwise people will just hack in all day long)

I know PHP provides a simple way around SQL Injection attacks (but I don't know PHP so I don't know what the correct syntax is). You mentioned that someone else had recommended using Stored Procedures: that would be a better and more secure method. But if you are not familiar with them then there is more learning you will need in order to gain knowledge about how to build and use them.

3) Don't store blank strings in fields that you do have a specific known value for. Just leave then out and they will default to whatever default value is set for them - probably NULL. Or explicitly store NULL instead of a blank string to indicate that the value is "unknown"
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts
Posted - 2010-12-07 : 12:35:17
good grief
to get round this change the column to allow null
alter table users alter column ID int null

Create a new table
create table usersID (ID int)
go
insert usersID select 16
go

Then add a trigger to update the ID value
create trigger tr_users on users for insert
as
if (select count(*) from inserted) > 1
begin
raiserror ('mutiple user inserts', 16, -1)
rollback tran
end

update users
set ID = (select max(ID) from users) + (select ID from usersID)
where ID is null

update usersID
set ID = case when usersID = 16 then 14 else 16 end
go

Now it should all work.
(All this could be in the insert SP.)

Another option would be to create an identity then have theis as a computed column
Or calculate the ID value in the client when it is read.

What do you want to happen if a user gets deleted.

A bit concerned that if you are giving meaning to the value that means it is needed for the application and as you have all the logic in the application it really should be assigned their.
But that means a read of the max value first which means locking tables.


==========================================
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

Kristen
Test

22859 Posts
Posted - 2010-12-07 : 12:43:21
"update users
set ID = (select max(ID) from users) + (select ID from usersID)
where ID is null"

Dangerous to be allowing NOT NULL through maybe Nigel? If someone forces a value then the 16/14 stepping will get out of wack?

I was thinking more of checking the two highest-value entries in table, or just the highest one: if a multiple of 30, or whether subtract-14 was a multiple of 30, (and if neither then just resume the progression by adding 14, perhaps)
Go to Top of Page
    Next 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