SQL statement - 'chop' phrase
in [Server Configuration]
|
Prev: Joines Ralated
Next: SQL 2005 Query Response Time
From: Plamen Ratchev on 23 Dec 2009 12:40 Geoff, Can you demonstrate SQL Injection using parameterized query with full-text search? Take as base the example we have been discussing here: DECLARE @keywords NVARCHAR(30); SET @keywords = N'brown fox'; SET @keywords = REPLACE(@keywords, ' ', ' AND '); SELECT * FROM FullTextTest WHERE CONTAINS(*, @keywords); I really do not think it is possible, but maybe I am missing something. -- Plamen Ratchev http://www.SQLStudio.com
From: Jay on 25 Dec 2009 19:41 Google "anatomy of a sql injection attack" or: http://en.wikipedia.org/wiki/Sql_injection or: http://www.computerworld.com.au/article/263257/anatomy_sql_injection_attack/?rid=-143 Basically, if you have a URL of: www.host.com/product.aspx?productid=12345 and the 12345 both is un-validated from the URL and goes directly into an SQL, the hacker can do the following: "www.host.com/product.aspx?productid=12345; select * from systables;" and that second SQL WILL EXECUTE! From there just about anything can be done, bounded by a hacker guide, or the hackers creativity. You're getting grief because most people here know this is opening your system to the outside world. "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:N6CdnWc_r_-Qy6_WnZ2dnUVZ_gNi4p2d(a)speakeasy.net... > Geoff, > > Can you demonstrate SQL Injection using parameterized query with full-text > search? Take as base the example we have been discussing here: > > DECLARE @keywords NVARCHAR(30); > > SET @keywords = N'brown fox'; > > SET @keywords = REPLACE(@keywords, ' ', ' AND '); > > SELECT * > FROM FullTextTest > WHERE CONTAINS(*, @keywords); > > I really do not think it is possible, but maybe I am missing something. > > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Erland Sommarskog on 26 Dec 2009 03:44 Jay (spam(a)nospam.org) writes: > Basically, if you have a URL of: www.host.com/product.aspx?productid=12345 > and the 12345 both is un-validated from the URL and goes directly into an > SQL, the hacker can do the following: > > "www.host.com/product.aspx?productid=12345; select * from systables;" > > and that second SQL WILL EXECUTE! Yes, if the code goes: cmd.CommandText = _ "SELECT col1, col2 ... FROM products WHERE product_id = " & _ strProdid But if the code goes cmd.CommandText = _ "SELECT col1, col2 ... FROM products WHERE product_id = @prodid" cmd.Parameters.Add("@prodid, SqlDBType.Int) cmd.Parameters("@from").Value = strProdid.ToInt32() There is no injection risk. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Jay on 26 Dec 2009 23:55 My position is easy. While everything should be validated and typed to begin with (just good programming), anything that even looks like it might open up a SQL injection attack should not be done, unless you KNOW what you're doing and really need to do it. I don't see my conditions being even close to met here. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CED6313CF94CYazorman(a)127.0.0.1... > Jay (spam(a)nospam.org) writes: >> Basically, if you have a URL of: >> www.host.com/product.aspx?productid=12345 >> and the 12345 both is un-validated from the URL and goes directly into an >> SQL, the hacker can do the following: >> >> "www.host.com/product.aspx?productid=12345; select * from systables;" >> >> and that second SQL WILL EXECUTE! > > Yes, if the code goes: > > cmd.CommandText = _ > "SELECT col1, col2 ... FROM products WHERE product_id = " & _ > strProdid > > But if the code goes > > cmd.CommandText = _ > "SELECT col1, col2 ... FROM products WHERE product_id = @prodid" > cmd.Parameters.Add("@prodid, SqlDBType.Int) > cmd.Parameters("@from").Value = strProdid.ToInt32() > > There is no injection risk. > > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 27 Dec 2009 05:50
Jay (spam(a)nospam.org) writes: > My position is easy. While everything should be validated and typed to > begin with (just good programming), anything that even looks like it > might open up a SQL injection attack should not be done, unless you KNOW > what you're doing and really need to do it. > > I don't see my conditions being even close to met here. It is always a good position when it comes to security that if you don't understand something to assume that it is insecure. Too many have been burnt because they made the opposite assumption. However, the obvious disadvantage with this position is that you lose a number of oppurtunities that are actually perfectly safe. The snippet I posted: cmd.CommandText = _ "SELECT col1, col2 ... FROM products WHERE product_id = @prodid" cmd.Parameters.Add("@prodid", SqlDBType.Int) cmd.Parameters("@from").Value = strProdid.ToInt32() is translated by the API to: EXEC sp_executesql N'SELECT col1, col2 ... FROM products WHERE product_id = @prodid', N'@prodid int', @prodid = @prodid Logically, this is equivalent to: CREATE PROCEDURE anonymous @prodid int AS SELECT col1, col2 ... FROM products WHERE product_id = @prodid go EXEC anonymous @prodid The important part here is that when you use parameters SQL injection cannot occur, because the SQL string is static. The only way this could be insecure is there are bugs in the API that permits buffer overruns. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |