|
I have been thinking a bit about sql injection (and have read Colin Angus Mackay's very good article SQL Injection Attacks and Some Tips on How to Prevent Them[^]).
I am already using stored procedures et al, but am now wondering what best practice would be for string replacement in parameters.
For SQL Server 2K5, I am already using:
Replace ' with ''
But should I extend this to include:
Replace ; with [empty string]
Replace drop[space] with [empty string]
Are there any other replacements that could be done to make string parameters safer, or is this approach just overkill?
Any thoughts?
|
|
|
|
|
RCoate wrote: I am already using stored procedures et al, but am now wondering what best practice would be for string replacement in parameters.
For SQL Server 2K5, I am already using:
Replace ' with ''
Why? If the string is arriving as a parameter there should be nothing to replace. Unless you aren't parameterising your query properly.
Does your SqlCommand.CommandText look like this:
EXEC MyStoredProcedure @Name='John O''Conner';
RCoate wrote: But should I extend this to include:
Replace ; with [empty string]Replace drop[space] with [empty string]
If you replace ; with an empty string that means you can only submit one command at a time. You would also risk altering perfectly legitimate data if your replace was indiscriminate. Ditto with "Drop"
For example: Let's say a column was to contain some free form text. That text could include:
My favourite Dutch confectionary is known as "drop zoute"; It is the best.
If you are not careful you will alter perfectly legitimate data.
If you are, as you claim, already using stored procedures then you have little to worry about. The parameters and command are separated out by the .NET Framework (if you are using it properly - by adding the parameters to the Parameters collection on the SqlCommand.
|
|
|
|
|
Colin Angus Mackay wrote: Does your SqlCommand.CommandText look like this:
EXEC MyStoredProcedure @Name='John O''Conner';
No, I use code sort of like this;
cmd.CommandText = "SprocName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = [open connection object]
Dim pFirstParameter As SqlParameter = SetParam("@ParamName", ParameterDirection, Datatype, DataSize)
pFirstParameter.Value = theValue
cmd.Parameters.Add(pFirstParameter)
dr = cmd.ExecuteReader
Where SetParam is a small function that returns a sqlparameter.
The general type of sproc I use is:
@FirstParam varchar(100)
AS
IF EXISTS(SELECT * FROM MyTable WHERE (SearchField=@FirstParam))
BEGIN
SELECT
Field1,
Field2,
...
FieldN
FROM
MyTable
WHERE
SearchField=@FirstParam
END
For my purposes, One command at a time is all I want and I am aware of the problem with restricting free text. The thing is I have very few free text fields - Most of the stuff I am doing is membership/authentication, so I am primarily thinking of user names and passwords. User names are assigned (users can not choose) and I can put in a list of forbidden characters/terms if needed.
My question is really, how far should you take user data sanitation?
|
|
|
|
|
RCoate wrote: My question is really, how far should you take user data sanitation?
As far as is practical. But don't overdo it either. Don't sanitise stuff out where the framework already assists you.
There is nothing wrong with apostrophes when you use parameters properly.
If you must build up dynamic SQL (and there are a few cases where it is necessary) then check things like do the table and column names that you are dynamically adding to the SQL. Do they really exist? If not reject it.
|
|
|
|
|
--find day,month,year
--for day
select datediff(d,'01 may 2008',getdate())
--
--for month
select datediff(m,'01 jun 2006',getdate())
--
--for year
select datediff(year,'01 jun 2006',getdate())
above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively.
but I want completely so I can use this in case of expired user in my project.
User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
You can subtract dates from each other without using the datediff, which makes for easier comparison of Dates.
This means you could do something along the line of:
<br />
IF GetDate() - '01 may 2008' > DateAdd(MONTH,3,'1900-01-01 00:00:00.000') <br />
PRINT('Current Date is more then 3 months from 01 may 2008')<br />
ELSE <br />
PRINT('Current Date is less then, or equal to, 3 months from 01 may 2008')<br />
Edit: a more simple method might be:
<br />
<br />
IF GetDate() - DateAdd(MONTH, 3, '01 may 2008') > 0<br />
PRINT('Current Date is more then 3 months from 01 may 2008')<br />
ELSE<br />
PRINT('Current Date is less then, or equal to, 3 months from 01 may 2008')<br />
This works in SQL Server 2005 at least.
Here I utilize the fact that internally datetime is represented by an integer, which means I can check it against 0 (which in SQL Server is the same value as 1900-01-01 00:00:00.000)
Clean the GetDate for its time portion to make the comparison more exact (or if you use SQL Server 2008, there is a Date datatype without the time portion).
|
|
|
|
|
thanks a lot
I have got what I wanted because of your kind help.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
Hi guys,
need some help.
i am trying to create a Full text index query for searching all the records having words starting with a particular character for eg. 'A'
i tried using Contains Clause with '*' wild card
my query is :-
select * from table where contains (columname,'"A*"');
results are coming fine but the problem is that it returns all the records in which there is any word between the string starting with A .
I need only those records in which the starting char of the column is 'A'
similar to the query -
select * from table where columname like 'A%' but i dont want to use like query as it will slower the results ..
any help will be appreciated
abhinav
|
|
|
|
|
Contains appears to be working exactly like it should, you'll have to use like or construct a more complicated contains clause, if you're using sql server 2k the transact sql help in the query analyzer tool has some decent examples and a rather good explination for almost anything sql.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
select count(*) as [No of Callers] from
(select empcode,empname,count(*) as [No of Calls],convert(varchar(10),details.Logintime ,101) as Date
from details join employee on employee.empid = details.empcode
where employee.Permission <> '1' and details.Logintime between '06/10/2006' and '06/11/2006'
group by empcode,empname, convert(varchar(10),details.Logintime ,101))e
i have this query to get count of callers.But i want date wise count of callers from table name status details.
just like this
date No of callers
06/07/2006 45
06/07/2007 50
can any one help me
thanks
|
|
|
|
|
Would it be something like:
select Logintime as [date], count(*) as [No of Callers] from
(select details.Logintime, empcode,empname,count(*) as [No of Calls],convert(varchar(10),details.Logintime ,101) as Date
from details join employee on employee.empid = details.empcode
where employee.Permission <> '1' group by empcode,empname, convert(varchar(10),details.Logintime ,101))e
Didn't bother to try running it, so I don't know if it works as is.
|
|
|
|
|
I'm having a locking issue I think.
Rather than post a whole heap of code, here is basically what I'm trying:
Begin trans
Insert <stuff> into table1
Insert <stuff> into table2
Insert into table3 select <stuff> from table1, table2, table3 plus a few other unmodified tables.
Commit trans
Basically what seems to be happening is that the select on 3rd statement returns no records (because they are locked). After committing, same select returns exactly what it should.
I could probably do this with several non-nested transactions, but that kind of defeats the purpose of the transaction.
I have tried SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before and during the main trans. Also tried wrapping the first two inserts in begin / commit and SELECT FROM WITH(NOLOCK).
Have googled nolock and also TRANSACTION ISOLATION LEVEL but not really come up with anything useful.
Does anyone have any ideas or can point me in the direction of a URL with some information please?
modified on Monday, June 16, 2008 4:33 AM
|
|
|
|
|
Strange - I just tried to replicate said behaviour and I had no issues with inserting in a table, selecting from said table and inserting into another table while within the same transaction.
Further more the things you state you have tried should be - AFAIK - enough/the correct method to solve issues similar to this.
Are you sure - 100% - that your "insert into table 3" query runs as it should? And that there aren't another problem then with the isolation level?
How do you call the transaction - via a code layer? Or directly as a query? - If the first, then try to run a simple example directly as a query and see if that gives you problems.
Could be the problem is completely elsewhere?
|
|
|
|
|
I did exactly the same thing, it all worked as expected - and I agree, AFAK it is the correct way to do it. I think you are correct, the problem is elsewhere. I do know in the past I have employed this technique many times without problems.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for the replies.
Took the whole thing apart and ran each query separately, works as expected.
Put back together, commented out the 3rd INSERT to just leave select. Pulls no records as expected.
Commented out select and hardcoded insert with some data that I knew insert1 & 2 would create and works fine.
Headbutted wall for a while, cracked open a bottle of wine, rebooted server and whadaya know...Works perfectly. Removed WITH(NOLOCK)s and... works perfectly. Removed READ UNCOMMITTED, works perfectly.
No idea what caused it. Could have been some other lock, although nothing else should have had a lock.
Its all marked for some serious testing and I'll see if its reproducable!
Anyway, thanks for the help. Hope I can reciprocate one day.
|
|
|
|
|
Hehe. Stuff which should work, but doesn't sporadically .... always good fun to debug 
Well, at least you got it running... more or less
|
|
|
|
|
I am getting tired of fighting with VC6.0 SQL syntax.
Why is this failing with - missing operator - error?
What operator?
I just want records which match multiple conditions.
It did not like comma separated WHERE also.
Could somebody please help me.
CString strSQL = "SELECT * FROM [Log] WHERE [Callsign] = Callsign and [Band] = Band ORDER BY [ContactID] DESC ; ";
Thanks for reading
Vaclav
<div class="ForumMod">modified on Sunday, June 15, 2008 12:58 AM</div>
|
|
|
|
|
try
CString strSQL = "SELECT * FROM [Log] WHERE [Callsign] = 'Callsign' and [Band] = 'Band' ORDER BY [ContactID] DESC ; ";
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Thanks - it works.
So why the stupid error code could not tell me that?
Especially when the VC replaces the brackets with single quotation marks.
I am not sure quotation marks should be called "operators".
Thanks again.
|
|
|
|
|
No problem.
When in condition you have value string then it must placed inside single quotes.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
So the SQL ends up looking like this 'Table' = 'Condition string'.
I'll try [Table] = [Condition string] to see hat VC will do with that.
|
|
|
|
|
No
Vaclav_Sal wrote: 'Table' = 'Condition string'.
but
select * from TableName where ColumnName='ConditionString'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
hi friends,
I have a problem with web user control with firing events using AjaxPopupcontrol.
my need is, when I mouse hover the text - some other text will display as a
popuptext from database. this is working well in normal aspx pages.
but when I place the popupextenwer and other controls to the Web User Control and
place the web user control to one aspx page. it doesn't work. mouse hover worked but not getting values from database. it returns the following error message!!!
Web Service Call Failed : 500
can any one explain how to do this. giv me a code for this.
Thanks in Advance!!!
Sabarees
|
|
|
|
|
Wow - SQL questions in the ASP.NET forum, and now ASP.NET questions in the SQL forum ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
I guess one day he/she will get idea on posting questions in right forum.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
