Block symbol entries (Either Table Design/Proc.)

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 2008 Forums

Transact-SQL (2008)

Block symbol entries (Either Table Design/Proc.)

Author

Topic

ledude
Starting Member

7 Posts

Posted - 2014-03-06 : 14:35:32

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-06 : 15:09:57

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.

ledude
Starting Member

7 Posts

Posted - 2014-03-06 : 15:37:50

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)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-06 : 16:59:27

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-06 : 17:03:18

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-06 : 17:14:04

I'd implement a check constraint.

ledude
Starting Member

7 Posts

Posted - 2014-03-06 : 18:37:14

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

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-06 : 19:42:28

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.

ledude
Starting Member

7 Posts

Posted - 2014-03-06 : 21:31:59

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-07 : 11:13:06

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-07 : 12:30:07

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

ledude
Starting Member

7 Posts

Posted - 2014-03-07 : 16:35:17

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-07 : 16:56:22

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-07 : 16:59:16

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.

ledude
Starting Member

7 Posts

Posted - 2014-03-11 : 22:37:18

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.

ledude
Starting Member

7 Posts

Posted - 2014-03-18 : 15:19:46

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-18 : 17:09:41

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources