SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Block symbol entries (Either Table Design/Proc.)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ledude
Starting Member

7 Posts

Posted - 03/06/2014 :  14:35:32  Show Profile  Reply with Quote
Sorry if i'm posting in the wrong section, i'm kind of a newbie when it comes to sql.

My problem is, i have a little game server, and some people has been abusing the nickname feature it has, using Thai, Japanese, and Empty symbols to exploit and try to escape.

I'd like to now if there is any way to block all symbol entries to a specific table.

It's currently a varchar, and i'd like to enable only numbers, uppercase letters, lowercase letter, and '(' ')'.

I thought i could fix that by changing the table design or the procedure, but i don't know what to enter to fix that. So, if anyone can help me with that, here's the procedure:

quote:
USE [Game]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProcUpdateNickName] (
@USERID VARCHAR(50),
@NEWNICKNAME VARCHAR(32)

AS

BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @DUP INT
DECLARE @ERROR INT
DECLARE @OLDNICKNAME VARCHAR(32)
DECLARE @CHANNELFLAG INT
, @DOMAINID VARCHAR(32)



SET @DUP = 0
SET @ERROR = 0


IF NOT EXISTS (
SELECT 1
FROM Game_MEMBER_INFO WITH (NOLOCK)
WHERE CHARINDEX('%S', @NEWNICKNAME) > 0
) BEGIN

IF ( DATALENGTH(LTRIM(RTRIM(@NEWNICKNAME))) > 0 ) BEGIN

SELECT @OLDNICKNAME = NICKNAME
, @CHANNELFLAG = CHANNELFLAG
, @DOMAINID = DOMAINID
FROM DBO.Game_MEMBER_INFO
WHERE USERID = @USERID


SELECT @DUP = COUNT(USERID)
FROM ( SELECT USERID COLLATE Latin1_General_CI_AS AS USERID, NICKNAME COLLATE Latin1_General_CI_AS AS NICKNAME
FROM DBO.Game_MEMBER_INFO A
WHERE NICKNAME = @NEWNICKNAME AND USERID != @USERID

) C

IF (@DUP = 0) BEGIN
UPDATE DBO.Game_MEMBER_INFO SET NICKNAME = @NEWNICKNAME WHERE USERID = @USERID
SELECT @ERROR = @@ERROR


IF (@ERROR != 0) BEGIN
IF (@ERROR = 2627) BEGIN
SELECT RETURNVAL = 3
END ELSE BEGIN
SELECT RETURNVAL = @ERROR


END
RETURN
END ELSE BEGIN

INSERT DBO.PY_NICK_LOG(ID,OLDNICKNAME,NEWNICKNAME) VALUES(@USERID,@OLDNICKNAME,@NEWNICKNAME)
SELECT RETURNVAL = 0

END
RETURN
END ELSE BEGIN

SELECT RETURNVAL = 2
RETURN

END
END ELSE BEGIN
SELECT RETURNVAL = 9
END

END ELSE BEGIN
SELECT RETURNVAL = 3
END





SET NOCOUNT OFF
END

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 03/06/2014 :  15:09:57  Show Profile  Reply with Quote
I did not quite follow your logic, but if the nicknames are inserted or updated in the table only through your stored procedure, you can check if the new nickname complies with your rules using code like shown below:
IF (@NEWNICKNAME NOT LIKE '%[^a-zA-Z0-9 ]%')
BEGIN
	PRINT 'Good nickname!'
END
ELSE
BEGIN
	PRINT 'Bad nickname!'
END
If there are multiple paths to accessing/updating the table, you would need to add a constraint or trigger on the table that enforces similar rules.
Go to Top of Page

ledude
Starting Member

7 Posts

Posted - 03/06/2014 :  15:37:50  Show Profile  Reply with Quote
I Want it not to allow characters other then a-z, A-Z, 0-9 and ( ).
I added ur line. It worked to block (alt+255) empty nicknames, but adding symbols is still working. They still can make nicknames such as ¼,-, ½, +, and other symbols. I want to to block all symbol entries, even if it's 'NickName¼', i only want letters, numbers and ( ), because the emoticons in the game are like (emot1), so, i want it to be okay to use 'Name(emot1)' and not anything else.

And no, this is the only procedure this function uses. And about 'my logic', would be to either block it on the procedure, or block the entry on the Table design (such as trying to Add 'abc' to an Int table)

Edited by - ledude on 03/06/2014 15:44:25
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
364 Posts

Posted - 03/06/2014 :  16:59:27  Show Profile  Reply with Quote
The only really thorough way is a trigger on the table to reject an INSERT or UPDATE with bad data. For example:


CREATE TRIGGER Game_MEMBER_INFO__TRG
ON DBO.Game_MEMBER_INFO
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM inserted WHERE NICKNAME LIKE '%[^ 0123456789A-Za-z]%')
BEGIN
    RAISERROR('Invalid characters found in "NICKNAME"; NICKNAME must contain only 0-9, A-Z or blank.', 16, 1)
    ROLLBACK TRANSACTION
END --IF
GO

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 03/06/2014 :  17:03:18  Show Profile  Reply with Quote
There is something else that is going on. That code should block 'NickName¼' - the example below prints out 'Bad nickname!'
DECLARE @NEWNICKNAME VARCHAR(32) = 'NickName¼';

IF (@NEWNICKNAME NOT LIKE '%[^a-zA-Z0-9 ]%')
BEGIN
	PRINT 'Good nickname!'
END
ELSE
BEGIN
	PRINT 'Bad nickname!'
END
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/06/2014 :  17:14:04  Show Profile  Reply with Quote
I'd implement a check constraint.
Go to Top of Page

ledude
Starting Member

7 Posts

Posted - 03/06/2014 :  18:37:14  Show Profile  Reply with Quote
As i said guys, i don't understand all that much of SQL codes, so, here's what i tried to do:

I changed the line:
quote:

IF (@DUP = 0) BEGIN
UPDATE DBO.Game_MEMBER_INFO SET NICKNAME = @NEWNICKNAME WHERE USERID = @USERID
SELECT @ERROR = @@ERROR



To
quote:

IF (@DUP = 0) BEGIN
IF (@NEWNICKNAME NOT LIKE '%[^a-zA-Z0-9 ]%')
BEGIN
UPDATE DBO.Game_MEMBER_INFO SET NICKNAME = @NEWNICKNAME WHERE USERID = @USERID
SELECT @ERROR = @@ERROR



Since that's the line that does the update, i thought i should add it before the command.

I don't think i can add those 'Print' lines as it's a procedure the Game client uses, it could mess it, and the ELSE/END i gave me an error when i tried to add. I don't think it'd be necessary tho.

About that trigger solution, i didn't really understand what i'd need to do with it. I tried adding it to the beginning of the code, but just gave me an error. Sorry, i don't know how that function work.

And since i'm a newbie, i don't know how i could implemente a check constraint either.

Edit:
Oh, and i also tried adding
IF (@NEWNICKNAME NOT LIKE '%[^a-zA-Z0-9 ]%')
At the beginning of the code. The result was the same.

Edited by - ledude on 03/06/2014 18:38:51
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 03/06/2014 :  19:42:28  Show Profile  Reply with Quote
quote:
I don't think i can add those 'Print' lines as it's a procedure the Game client uses, it could mess it, and the ELSE/END i gave me an error when i tried to add. I don't think it'd be necessary tho.
Those print statements were meant only to show you how it should work. You would put whatever business rules you need to implement in the if block or the else block or the case may be.

To keep things simple, you can try the following:

1. Run the code that I had posted from SSMS query window and make sure you are getting the output as expected.

2. Implement the trigger that Scott posted. Make no changes to your original stored procedure.

That should do it.
Go to Top of Page

ledude
Starting Member

7 Posts

Posted - 03/06/2014 :  21:31:59  Show Profile  Reply with Quote
quote:
DECLARE @NEWNICKNAME VARCHAR(32) = 'NickName¼';

IF (@NEWNICKNAME NOT LIKE '%[^a-zA-Z0-9 ]%')
BEGIN
PRINT 'Good nickname!'
END
ELSE
BEGIN
PRINT 'Bad nickname!'
END


This code printed 'Good Nickname', if i switch it to 'LIKE', it prints 'Bad Nickname'

Edit:
quote:

CREATE TRIGGER Game_MEMBER_INFO__TRG
ON DBO.Game_MEMBER_INFO
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM inserted WHERE NICKNAME LIKE '%[^ 0123456789A-Za-z]%')
BEGIN
RAISERROR('Invalid characters found in "NICKNAME"; NICKNAME must contain only 0-9, A-Z or blank.', 16, 1)
ROLLBACK TRANSACTION
END --IF
GO


As for the trigger, if i try to run the code on the procedure it returns an error, it also returns error if i use as a new query. On a new query ir returns that the object 'DBO.Game_MEMBER_INFO' doesn't exist.
P.S. I already tried adding the main database to the line 'Game.DBO.Game_MEMBER_INFO', same error.

Edited by - ledude on 03/06/2014 21:44:16
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/07/2014 :  11:13:06  Show Profile  Reply with Quote
I don't know if this helps, but here is how you can implement a check constraint:
CREATE TABLE dbo.Game_Member
(
	ID INT NOT NULL IDENTITY(1,1)
	,NickName VARCHAR(50) NOT NULL
	,CONSTRAINT CHK_GameMember_NickName CHECK (NickName NOT LIKE '%[^a-zA-Z0-9()]%')
)

INSERT dbo.Game_Member (NickName) VALUES ('Foo') -- Succeed
INSERT dbo.Game_Member (NickName) VALUES ('Test_') -- Fail
INSERT dbo.Game_Member (NickName) VALUES ('&^')	-- Fail
INSERT dbo.Game_Member (NickName) VALUES ('Bar') -- Succeed
INSERT dbo.Game_Member (NickName) VALUES ('+') -- Fail
INSERT dbo.Game_Member (NickName) VALUES ('(woot)') -- Succeed

DROP TABLE dbo.Game_Member
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 03/07/2014 :  12:30:07  Show Profile  Reply with Quote
quote:
Originally posted by ledude

quote:
DECLARE @NEWNICKNAME VARCHAR(32) = 'NickName¼';

IF (@NEWNICKNAME NOT LIKE '%[^a-zA-Z0-9 ]%')
BEGIN
PRINT 'Good nickname!'
END
ELSE
BEGIN
PRINT 'Bad nickname!'
END


This code printed 'Good Nickname', if i switch it to 'LIKE', it prints 'Bad Nickname'


This is a problem. This MUST print "Bad nickname!". If it does not, there is something fundamentally wrong. Are you using Microsoft SQL Server? Are you copying the code exactly as it is and running it? If both of those are true, there is NO way it will print "Good nickname!". If it is, you have to figure out why it is doing so. Until you figure that out, whatever you are going to be doing is not going to be logically correct.


To help you figure it out, run this code and see what it prints out. This is just a simplified version:
DECLARE @NEWNICKNAME VARCHAR(32) = 'X';

IF (@NEWNICKNAME NOT LIKE '%[^X]%')
BEGIN
	PRINT 'Good nickname!'
END
ELSE
BEGIN
	PRINT 'Bad nickname!'
END
Go to Top of Page

ledude
Starting Member

7 Posts

Posted - 03/07/2014 :  16:35:17  Show Profile  Reply with Quote
quote:

DECLARE @NEWNICKNAME VARCHAR(32) = 'X';

IF (@NEWNICKNAME NOT LIKE '%[^X]%')
BEGIN
PRINT 'Good nickname!'
END
ELSE
BEGIN
PRINT 'Bad nickname!'
END


This prints Good Nickname. I'm using Microsoft SQL 2008 r2.

That constraint, what exactly does it do? Should i add it to the procedure or execute on a new query?

As for the trigger, still haven't figured what's wrong there, anyone? =S
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 03/07/2014 :  16:56:22  Show Profile  Reply with Quote
Lamprey was showing an example of how you would create a table and apply the constraint on it all at once. Since you already have the table, you can add the constraint like this:
ALTER TABLE DBO.Game_MEMBER_INFO  ADD CONSTRAINT CHK_GameMember_NickName CHECK (NickName NOT LIKE '%[^a-zA-Z0-9()]%')
That is ALL you should need to do. Make sure you cleanup the table of any invalid nick names before you apply the constraint.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 03/07/2014 :  16:59:16  Show Profile  Reply with Quote
quote:
Originally posted by ledude

quote:

DECLARE @NEWNICKNAME VARCHAR(32) = 'X1';

IF (@NEWNICKNAME NOT LIKE '%[^X0-9]%')
BEGIN
PRINT 'Good nickname!'
END
ELSE
BEGIN
PRINT 'Bad nickname!'
END


This prints Good Nickname. I'm using Microsoft SQL 2008 r2.


Now if you make the first change shown in red (and only that), it should say bad nickname. Then if you add the second change in red, it should say good nick name.

So you are putting all the range of characters you want to allow in the NOT LIKE clause. Given that, I don't see how the example I originally posted gave you the wrong results.
Go to Top of Page

ledude
Starting Member

7 Posts

Posted - 03/11/2014 :  22:37:18  Show Profile  Reply with Quote
Sorry guys, had a busy weekend, i'll be testing the fixes you sent tomorrow most likely. Sorry for the delay on reporting about it.
Go to Top of Page

ledude
Starting Member

7 Posts

Posted - 03/18/2014 :  15:19:46  Show Profile  Reply with Quote
Alright, the constraint did not work, but i managed to make the NOT LIKE command work by adding every character instead of a-z 0-9.

Sorry for the super late report, and thanks for everyone that helped!!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 03/18/2014 :  17:09:41  Show Profile  Reply with Quote
If you are on SQL Server - and you said you were - the "a-z" construct should work. I am suspecting that the dash that you are using is, for some reason, not the dash that corresponds to CHAR(45)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000