Can TSQL 2005 do this : ToBase64string

Question

Can TSQL 2005 do this : ToBase64string

Digs

SSCertifiable

Points: 7333
More actions
December 30, 2009 at 11:22 am

#141395

I have this code in vb.net, I use it to convert info in my query string to code like
ID = 1000, the result would - HgYt
Public Shared Function Encrypt(ByVal str As String) As String
Return Convert.ToBase64String(Text.Encoding.ASCII.GetBytes(str))
End Function
Public Shared Function Decrypt(ByVal str As String) As String
Return Text.Encoding.ASCII.GetString(Convert.FromBase64String(str))
End Function
IT would make my life easier if I could do the same 'convert to' and 'convert from' in TSQL function within SLQ 2005, so can this be done, are there simple functions that can do this (or is this a massive project to write some code)..please advise???

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

It depends... is the ToBase64String function something that you wrote or not? If it is, post it and someone may be able to come up something.

As an alternative, you could use the existing code as a CLR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323546 More actions · Answer 2

also use the search feature here on SSC for "Base64"; there are a number of forum posts and submitted scripts to encode/decode;

here's the first of many i found:

BASE64 Encode and Decode in T-SQL[/url]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 3

of course you should also pose the question : WHY ??

If this is some kind of "primitive" encryption, SQLServer nowadays has a better alternative !

Is it to confuse network sniffers ?

SQL2005 uses ssl encrypted comunication, so your apps can do that too.

Is is to protect your developed system ?

eventually queries must be run, so profiler will still show that to a dba.

It will also be a hell of a job to simply debug your apps and/or solve performance issues, (dead)locking issues,...

It's not because SQLServer CAN do it (in any way), one should use it just because the technology exists.

Best wishes for 2010

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

I just looked up and read some of the articles and scripts for this Base64 "encryption". My suggestion would be... don't bother. It's an easy hack from what other folks have said. Deploying homegrown encryption techniques are a sure-fire way to experience a career changing event, if you know what I mean.

If you happen to be working for a bank and you deploy this for them, please let me know which bank it is so I can get rich overnight. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Digs SSCertifiable Points: 7333 More actions · Answer 5

Ok thanks..

The base64 encryption does not hide anything too secret, I am just using a simple method to keep the www idiots at bay.

Even if the code is cracked little will be gained from it. 😛