April 15, 2010 at 12:11 pm
Why does the @C show nothing? (See results section)
declare @gcASCII char(300), @a CHAR(10), @b-2 char(10), @char CHAR(1), @C char(10)
DECLARE @counter int, @len int, @x char(1)
SET @a= 'MIKE'
set @b-2= ''
set @C= ''
SET @len= LEN( @a)
SET @counter= 1
SET @char = substring( @a, @counter, 1)
WHILE @counter <= @len
BEGIN
set @x = char( 255 - ascII( @char))
print @x
set @counter += 1
SET @char = substring( @a, @counter, 1)
END
print 'len: ' + cast( @len as char(10))
print 'a: ' + @a
print 'b: ' + @b-2
print 'c: ' + @C + '--'
Results:
²
¶
´
º
len: 4
a: MIKE
b: EKIM
c: --
April 15, 2010 at 12:23 pm
I don't have a system to try this on, but I think if you change the definition of @C to varchar, you'll find it will work. Remember, a char(10) will always be ten spaces so if you set it eqaul to itself plus another character and truncate the result to fit into 10 characters, you'll just keep setting it to itself over and over again because it will drop the concatenated character.
April 15, 2010 at 12:25 pm
--Make @C a varchar as in the following:
declare @gcASCII char(300), @a CHAR(10), @b-2 char(10), @char CHAR(1), @C varchar(10)
DECLARE @counter int, @len int, @x char(1)
SET @a = 'MIKE'
set @b-2 = ''
set @C = ''
SET @len = LEN( @a)
SET @counter = 1
SET @char = substring( @a, @counter, 1)
WHILE @counter <= @len
BEGIN
set @x = char( 255 - ascII( @char))
print @x
set @counter = @counter + 1
SET @char = substring( @a, @counter, 1)
END
print 'len: ' + cast( @len as char(10))
print 'a: ' + @a
print 'b: ' + @b-2
print 'c: ' + @C + '--'
April 15, 2010 at 12:25 pm
David beat me to it. . .
April 15, 2010 at 12:26 pm
That's correct. You're adding to the end of a full string.
You either need to set it to varchar, or turn off ANSI Padding.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2010 at 12:35 pm
If all you're really trying to do is reverse the string, why not use reverse()?
April 15, 2010 at 12:46 pm
declare @a CHAR(10), @b-2 char(10);
SELECT @a = 'MIKE'
-- Encrypt
SELECT
(SELECT char( 255 - ascII(SUBSTRING(@a, number, 1)))
FROM Common.dbo.Numbers
WHERE number BETWEEN 1 AND LEN(@a)
FOR XML PATH(''), TYPE).value('.[1]','varchar(10)');
SELECT @b-2 = (SELECT char( 255 - ascII(SUBSTRING(@a, number, 1)))
FROM Common.dbo.Numbers
WHERE number BETWEEN 1 AND LEN(@a)
FOR XML PATH(''), TYPE).value('.[1]','varchar(10)');
-- Decrypt
SELECT
(SELECT char( 255 - ascII(SUBSTRING(@b, number, 1)))
FROM Common.dbo.Numbers AS N
WHERE number BETWEEN 1 AND LEN(@b)
FOR XML PATH(''), TYPE).value('.[1]','varchar(10)');
If you don't have a Numbers table:
CREATE TABLE [dbo].[Numbers](
[Number] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO dbo.Numbers (Number)
SELECT TOP 10001 ROW_NUMBER() OVER (ORDER BY T1.object_id)-1
FROM sys.columns T1
CROSS JOIN sys.columns T2;
I have mine in a database named "Common". You'll need to change the encrypt and decrypt queries to use your database name.
Please note that this kind of encryption is considered extremely weak. It's simple character substitution, and on any reasonably sized sample-set, it can be cracked pretty easily. If you're using it for hobby/entertainment, that's fine. Don't use it in a business-critical situation where real security is necessary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2010 at 12:50 pm
Forgot one part. Add "ORDER BY number" between the Where clause and the For XML clause. It'll usually work without that, but isn't guaranteed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 18, 2010 at 11:24 pm
SQL Server provides extensive built-in support for encryption, there's no need to roll your own.
See Cryptographic Functions (Transact-SQL)
Also, if you were to implement some kind of custom routine like this (for reasons that escape me), a SQLCLR routine would likely provide significant benefits.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2010 at 3:26 pm
Heh... you know what they call people who try to "roll their own" when it comes to encryption? Hacked and unemployed.
Don't try to write your own encryption or even a mild obfuscation unless you happen to know a whole lot about cryptology.
--Jeff Moden
April 19, 2010 at 7:03 pm
What would you recommend for storing credit card information? Using 2008 encryption or a 3rd party?
April 19, 2010 at 7:11 pm
mike 57299 (4/19/2010)
What would you recommend for storing credit card information? Using 2008 encryption or a 3rd party?
Either, so long as it meets the requirements. If you are storing CC info, you probably need to comply with:
https://www.pcisecuritystandards.org/security_standards/pci_dss.shtml
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2010 at 9:55 pm
Paul White NZ (4/19/2010)
mike 57299 (4/19/2010)
What would you recommend for storing credit card information? Using 2008 encryption or a 3rd party?Either, so long as it meets the requirements. If you are storing CC info, you probably need to comply with:
https://www.pcisecuritystandards.org/security_standards/pci_dss.shtml
3rd party. Built-in means SQL knows how to decrypt it, so it ain't much of an encryption. Besides - you get better access to stronger algorithms.
That said - is there a GOOD reason to store them at all? I shudder every time I hear that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 19, 2010 at 10:23 pm
Yes, we have to store them as we offer "card on file" options. Do you have any good 3rd party software that can be used in SQL, .net operations and activex.
Thanks,
Mike
April 19, 2010 at 10:41 pm
Matt Miller (#4) (4/19/2010)
3rd party. Built-in means SQL knows how to decrypt it, so it ain't much of an encryption.
Not sure what you mean here, Matt. Are you suggesting that some component of SQL Server is always able to decrypt anything encrypted using one of the built-in functions? :unsure:
Besides - you get better access to stronger algorithms.
SQL Server provides a range of algorithms that are suitable for most purposes, AES-256 for example. Private keys can be up to 2048 bits. An external provider can be used via EKM.
Cryptographic Functions (Transact-SQL)
Understanding Extensible Key Management (EKM)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply