compare binary with character - SQL Server Forums

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)

compare binary with character

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-01-15 : 15:57:49

hi
Select loginName, lastname fromUSERtable where loginName='"&login&"' and password='"&pass&"'

my password field is varbinary and my varible is chacarter and i have to compare these to field. Can any help me

Thanks In advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 16:56:50

You can explicitly convert the password column to VARCHAR with the CONVERT command. Take a look at CONVERT in BOL for which are implicit and which are explicit. If you provide a full T-SQL example, we'll be able to help you better.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-01-15 : 17:52:43

lets see this
Select loginName ,Password from USERtable where loginName='john' and password=convert(varbinary(50),'mike')

this password and login is a valid entry but when i run this query i dont have any result.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 17:57:12

It works fine for me. I suspect the problem is with the data in the password column. Check this out in Query Analyzer:

Create table table1
(login varchar(50), password varbinary(50))

insert into table1 values('john', 0x6D696B65)

select login, password
from table1
where password = convert(varbinary(50), 'mike')

drop table table1

0x6D696B65 is the varbinary version of mike.

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-15 : 18:52:31

I just thought you should know that your SELECT statement here is a textbook example of how to leave yourself open to a SQL Injection attack. You might want to read up on this subject. Here's a link to get you started: http://www.securiteam.com/securityreviews/5DP0N1P76E.html

The varbinary conversion changes it a little bit, but still a potential risk.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources