How to Find HTML Tags and SQL Scripts in a column of table

Question

How to Find HTML Tags and SQL Scripts in a column of table

sweath

SSC-Addicted

Points: 499
More actions
October 19, 2010 at 1:25 pm

#241884

Hi All,
I have a Task where i will be getting a file on dailybasis in that i have a column called "Notes"
before loading that file into our DB i have to check wether there are any SQL Scripts or HTML tags in that particular column called NOTES
so that it wont effect our DB like(SQLINJECTION),the column NOTES is of Datatype Varchar(5000),
FYI that column has text about the customer and Agent discussion in less than 5000 words
so can some one suugest me how to create an SSIS package or write a SQl query to Filter those HTML and SQL SCRIPTS in that particular Column.
Thanks in Advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i use a regualr expression to remove what i think of as "malicious" keywords from a simple web page that is supposed to provide SELECT only statements;

this would be the TSQL equivilent:

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'SELECT ','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'ALTER','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'DBCC','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'MERGE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'BULK','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'DBREPAIR','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'REVOKE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'TRUNCATE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'DELETE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'OPEN','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'DROP','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'UPDATE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'EXEC','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'XP_','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'EXECUTE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'GRANT','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'IDENTITY_INSERT','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'RECONFIGURE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'CONNECT','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'INSERT','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'REMIRROR','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'CREATE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'KILL','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'REMOVE','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'CURSOR','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'LOAD','----')

UPDATE YourTable Set YourColumn = REPLACE(YourColumn,'RESTORE','----')

i also have this snippet for stripping HTML from a string:

--===== Replace all HTML tags with nothing

WHILE CHARINDEX(' CHARINDEX('<',@HTMLText)

SELECT @HTMLText = STUFF(@HTMLText,

CHARINDEX('<',@HTMLText),

CHARINDEX('>',@HTMLText)-CHARINDEX('<',@HTMLText)+1,

'')

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!

sweath SSC-Addicted Points: 499 More actions · Answer 2

Hi Thanks for the prompt response,your scripts are helpful

but we are not aware what kind of HTML tags and the sqlscripts they might send us as we are getting the data feeds from the 3rd party we need to filter all this things before inserting the data into DB as i said the Column NOtes has the discussion between the customer and the Agents crazy agents are saving the data in what ever format they want and they are also saving all the URLS and other scripts so i am looking for the scripts to cleanse this column without using any specific list of KEYWORDS as they might use any keyword rt so......

Lowell SSC Guru Points: 323518 More actions · Answer 3

kancherla.ks (10/19/2010)
Hi Thanks for the prompt response,your scripts are helpful
but we are not aware what kind of HTML tags and the sqlscripts they might send us as we are getting the data feeds from the 3rd party we need to filter all this things before inserting the data into DB as i said the Column NOtes has the discussion between the customer and the Agents crazy agents are saving the data in what ever format they want and they are also saving all the URLS and other scripts so i am looking for the scripts to cleanse this column without using any specific list of KEYWORDS as they might use any keyword rt so......

well, the html snippet i posted strips all HTML out no matter what.

the problem with SQL is there's no way to "detect" without looking for a double dash (-- to start a comment followed by keyword commands....so you've got to use a keyword list for SQL, i think.

stating the obvious, if you are using parameters, you are safe from SQL injection anyway.

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!

sweath SSC-Addicted Points: 499 More actions · Answer 4

one record in our Notes column is like this

Notes:

nasal congestion pollen xyz valley/desire loss wt/>exercise <dietary intake

so i think the HTML which u gave me is replacing "<" and ">" from the above record which is not correct so expecting some thing that works with this

Help is really appreciated

Thanks