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.
| Author |
Topic |
|
KrafDinner
Starting Member
34 Posts |
 Posted - 2010-07-14 : 14:54:03
|
| Just out of sheer curiosity, what would happen if you used a replace on a text field for input to replace ' with '' ? How vulnerable would you be left to injection ?(I realize there are better practices, and am using a different approach, I was just wondering if this would be something that would still leave you wide open or not)That would still allow people to enter ' without being able to close the statement would it not ?Any input on this would be appreciated. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-14 : 15:40:15
|
quote:
How vulnerable would you be left to injection?
Completely. At best you'd throw a syntax error if they attempted injection, which could reveal information about your database structure. If the inputs are all numeric and didn't contain any ' characters, it could still be injected.There's some info on how to avoid SQL Injection here:http://bobby-tables.com/Although the cartoon contains a ', doubling it would not prevent injection. |
 |
|
|
KrafDinner
Starting Member
34 Posts |
Posted - 2010-07-14 : 22:32:54
|
| I'm using parameterized calls - I already knew it would be a bad idea not to, I was just curious if doubling it would stop the injection since putting '' would actually only add ' to the code... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-15 : 02:39:31
|
If you are using dynamic SQL (i.e. concatenating a string to make a SQL command, rather than passing parameters) replacing single-quote with two single-quotes, AND wrapping any user-supplied/foreign data elements in single quotes, will be fine (with caveats).INSERT INTO MyTable (MyColumn) VALUES ( xxx )if you just replace "xxx" with); DROP TABLE Students;--yeah, you have a problem! If you use'); DROP TABLE Students;--'it will be fine (provided any embedded single-quotes have been doubled-up)You would need to do this for EVERY such data element WITHOUT FAIL - including things that are numbers, dates, i.e. all things both Strings and Non-strigns too. (I imagine this places some strain on SQL converting strings to numbers; parameters are better, therefore, as they should retain native-format all the way from application to SQL.The caveat is if the SQL process itself does some dynamic SQL.ChangeINSERT INTO MYTable (MyColumn) VALUES ( xxx ) toEXEC MySproc @MyParameter and make a stored procedure:CREATE PROCEDURE MySProc @MyParameter varchar(1000)ASINSERT INTO MyTable (MyColumn) VALUES ( @MyParameter )EXEC ('INSERT INTO MyAuditTable (SomeColumn) VALUES (' + @MyParameter + ')' ) -- Oh dear!However, my rule still applies "do this for EVERY such data element WITHOUT FAIL" - its just that the programmer needs to remember, and realise that it is needed even here in the guts of a Stored ProcedureEXEC ('INSERT INTO MyAuditTable (SomeColumn) VALUES (' + dbo.fnDoubleQuotesAndWrap(@MyParameter) + ')' )would be fine. You would need to do this EVEN IF MySproc was called using parametrised methods. In fact if I did this I'd shoot myself! I would use sp_ExecuteSQL instead of EXEC so that I could use a parametrised query and avoid the string-concatenation altogether:EXEC sp_ExecuteSQL N'INSERT INTO MyAuditTable (SomeColumn) VALUES (@TheParameter)', N'@TheParameter varchar(1000)', @TheParameter = @MyParameter |
|
|
|
KrafDinner
Starting Member
34 Posts |
Posted - 2010-07-15 : 10:47:07
|
| That's sort of along the lines of what I was thinking... as long as everything was enveloped in single quotes and then any single quotes inside were doubled up, it should work... Again, an absolute mess to code, but a possibility.Parameterized statements are the way to go anyway.What happens if someone tries to put a single quote into a parameterized query ?Like let's say I have a varchar(2000) column for notes about a customer and someone puts "The customer's home is black and white." How does a parameterized query treat that ? Will the ' just be stored in the column ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-15 : 11:30:29
|
"an absolute mess to code"Do you think so?strSQL = "SELECT Col1, Col2, ... FROM MyTable WHERE 1=1"IF Request.NameField <> "" THEN strSQL = strSQL & " AND NameCol = " & fnSafeSQL(Request.NameField)IF Request.TownField <> "" THEN strSQL = strSQL & " AND TownCol = " & fnSafeSQL(Request.TownField) That would probably evolve tostrSQL = "SELECT Col1, Col2, ... FROM MyTable WHERE 1=1" _ & fnWhereClauseAND_IfNotBlank("NameCol", Request.NameField) _ & fnWhereClauseAND_IfNotBlank("TownField", Request.TownField )which I reckon is very readable (apart from my ridiculous function name!)Parametrised treats a data element as an object (if that's the right word), so it is passed "whole" and used "as is". Its only when you do string concatenation that a rogue single-quote, or similar, can disrupt the intended syntax.So even with parametrised there is still the risk that an Sproc does some dynamic SQL and breaks because of SQL Injection ...Then there are people putting <SCRIPT> into Web Form Fields to do Javascript cross-site scripting hacks ... |
|
|
|
|
|
|
|
|
Quick question about injection