December 7, 2011 at 4:28 am
Hello
I'm trying to insert html like the below into a table. Simple. But I am lazy and cannot be bothered to go through the string and put speech marks around the speech marks in order to make the select work.. is there a simple way to make this select work without going through the manual process?
INSERT dbo.Table1 (Html)
SELECT '<table cellpadding='0' cellspacing='0' border='0' id='Table2'><tr><td align='center'><img src='{0}' alt='' border='0'></td></tr><tr><td align='center'><a href='{1}'><script language='JavaScript'> document.write('<form name="jpform{2}"><input name="progressive{2}" id="progressive{2}" readonly style="background-color : transparent; border-bottom-color: #99ffff; border-bottom-width: 0px; border-left-color: #99ffff; border-left-width: 0px; border-right-color: #99ffff; border-right-width: 0px; border-top-width: 0px; color: #000000; font-family: verdana; font-size: 12px; font-weight: bold; height: 15px; width: 115px;text-align : center;"></form>');ScrollProgressiveCounters({2});</script></a></td></tr><tr><td align='center'> <select onchange='window.location.href=(this.options [this.selectedIndex].value + "?{4}=ree{3}&a=regerggarg")';><option value=''>Go</option> <option value='http://www.website.com'>Website</option><option value='http://www.website2.com'>Website2</option> <option value='http://www.j3.com'>Flash3</option> <option value='http://www.sdafsdfadsfa.com'>sadfasdf</option> <option value='http://www.dddddddddd.com'>dddddddddd</option> </select></td></tr></table>'
Muchos Gracias
December 7, 2011 at 4:42 am
I believe some application (third party / owned) is trying to insert this HTML.
I am not trying to help on your requirements here but I am trying to give you an idea on potential security issue i.e. SQL Injection because neither your application nor your T-SQL script is checking for ';' character.
SELECT @STR= 'Delete from tableA;Delete from tableB;Delete from tableC;'
EXEC (@STR) -- BOOOM!
December 7, 2011 at 7:41 am
By far the easiest and best way to deal with this is to use a stored proc and make this html string a parameter. Otherwise you need to do a replace on your string BEFORE it gets to sql. Replace all ' with ''.
That being said I have to agree with Dev. You probably need to check your application for sql injection vulnerability.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 7, 2011 at 7:50 am
I think it's best to replace all single quotes inside the string to double quotes before inserting the html code, like so:
INSERT dbo.Table1 (Html)
SELECT '<table cellpadding="0" cellspacing="0" border="0" id="Table2"><tr><td align="center"><img src="{0}" alt="" border="0"></td></tr><tr><td align="center"><a href="{1}"><script language="JavaScript"> document.write("<form name="jpform{2}"><input name="progressive{2}" id="progressive{2}" readonly style="background-color : transparent; border-bottom-color: #99ffff; border-bottom-width: 0px; border-left-color: #99ffff; border-left-width: 0px; border-right-color: #99ffff; border-right-width: 0px; border-top-width: 0px; color: #000000; font-family: verdana; font-size: 12px; font-weight: bold; height: 15px; width: 115px;text-align : center;"></form>");ScrollProgressiveCounters({2});</script></a></td></tr><tr><td align="center"> <select onchange="window.location.href=(this.options [this.selectedIndex].value + "?{4}=ree{3}&a=regerggarg")";><option value="">Go</option> <option value="http://www.website.com">Website</option><option value="http://www.website2.com">Website2</option> <option value="http://www.j3.com">Flash3</option> <option value="http://www.sdafsdfadsfa.com">sadfasdf</option> <option value="http://www.dddddddddd.com">dddddddddd</option> </select></td></tr></table>'
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 7, 2011 at 8:01 am
Erwin Dockx (12/7/2011)
I think it's best to replace all single quotes inside the string to double quotes before inserting the html code, like so:INSERT dbo.Table1 (Html)
SELECT '<table cellpadding="0" cellspacing="0" border="0" id="Table2"><tr><td align="center"><img src="{0}" alt="" border="0"></td></tr><tr><td align="center"><a href="{1}"><script language="JavaScript"> document.write("<form name="jpform{2}"><input name="progressive{2}" id="progressive{2}" readonly style="background-color : transparent; border-bottom-color: #99ffff; border-bottom-width: 0px; border-left-color: #99ffff; border-left-width: 0px; border-right-color: #99ffff; border-right-width: 0px; border-top-width: 0px; color: #000000; font-family: verdana; font-size: 12px; font-weight: bold; height: 15px; width: 115px;text-align : center;"></form>");ScrollProgressiveCounters({2});</script></a></td></tr><tr><td align="center"> <select onchange="window.location.href=(this.options [this.selectedIndex].value + "?{4}=ree{3}&a=regerggarg")";><option value="">Go</option> <option value="http://www.website.com">Website</option><option value="http://www.website2.com">Website2</option> <option value="http://www.j3.com">Flash3</option> <option value="http://www.sdafsdfadsfa.com">sadfasdf</option> <option value="http://www.dddddddddd.com">dddddddddd</option> </select></td></tr></table>'
In general you need to be careful about doing that. By changing from single quote to a double you are changing the data. For html this is not an issue but it generally a better practice to handle and store data as it is presented instead of modifying the data. There may be some reason that double quotes are not allowed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 7, 2011 at 8:05 am
Thanks people.
Re: potential injection attacks. This is an internally administered column that users won't be going near so should not be a problem.. do scream again if i've mised something!
December 7, 2011 at 8:19 am
most attacks come from the internal network, so saying your end users go no where near the table is great, but what about your internal users. you might have an employee who is a bit miffed with the company, knows a few things about sql, knows that this massive gaping hole is in your system and issue something like this
select 1; drop database MyDB; sp_dropserver 'SERVERNAME','droplogins';
oooops, you have just lost your database and your server and all your logins, one would say EPIC FAIL, hand in all your equipment, your P45's in the post, all because this one hole wasn't made that little bit smaller or completly filled.
December 7, 2011 at 8:22 am
so every column in every database should not be allowed ';' or the word 'drop' etc.?
How do you implement that?
December 7, 2011 at 8:25 am
you cant, you just need to make sure that you have a very tight server, logins are check for any permissions they shouldnt have, parameters being passed in to procedures are as small as possible, there are lots of good sites out there on the topic of SQL Injection, I would suggest giving them a go.
December 7, 2011 at 8:26 am
Again, the easiest way is to parameterize your queries. You can either parameterize your pass through queries or create a stored proc.
Store proc example:
create procedure Table1_Insert
(
@HTML varchar(max)
) as begin
INSERT dbo.Table1 (Html)
values @HTML
end
Then you just execute it with your huge html string.
For details about parameterizing a pass through query, it will depend on the language.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 7, 2011 at 8:28 am
this is an hilarious link
http://gizmodo.com/5498412/sql-injection-license-plate-hopes-to-foil-euro-traffic-cameras
someone trying to foil a euro country's speed camera sytem using SQL injection
December 7, 2011 at 8:28 am
It's not the data in the column, it's the validation of the input when it's entered. If you use a proc, and a call to SQL Server that explicitly lists parameters, the injection isn't allowed.
I know you say it's internal, but will it always be? Do you control the firewall(s)? Over half of them are incorrectly configured (Layers of security[/url]). Plus you want to build the habit of secure software. Don't just assume your users won't do something stupid, or malicious.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 7, 2011 at 8:32 am
anthony.green (12/7/2011)
this is an hilarious linkhttp://gizmodo.com/5498412/sql-injection-license-plate-hopes-to-foil-euro-traffic-cameras
someone trying to foil a euro country's speed camera sytem using SQL injection
That is funny.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 7, 2011 at 9:48 am
Sean Lange (12/7/2011)
In general you need to be careful about doing that. By changing from single quote to a double you are changing the data. For html this is not an issue but it generally a better practice to handle and store data as it is presented instead of modifying the data. There may be some reason that double quotes are not allowed.
@sean, I'm not in favor of changing source data either, but it seems that the op knows well enough what's being inserted. Your solution of using a procedure is better, but I posted my solution because the example was an ad-hoc query.
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 7, 2011 at 10:11 am
Erwin Dockx (12/7/2011)
Sean Lange (12/7/2011)
In general you need to be careful about doing that. By changing from single quote to a double you are changing the data. For html this is not an issue but it generally a better practice to handle and store data as it is presented instead of modifying the data. There may be some reason that double quotes are not allowed.@sean, I'm not in favor of changing source data either, but it seems that the op knows well enough what's being inserted. Your solution of using a procedure is better, but I posted my solution because the example was an ad-hoc query.
In those cases it is better to use a replace prior to executing your sql. Here is a c# example:
string html = "<table cellpadding='0' cellspacing='0' border='0' id='Table2'>...snip...</select></td></tr></table>";
string sql = "insert dbo.Table1 (Html) select " + html.Replace("'", "''");
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply