|
Dear All,
I need to set the value of a variable with the result of a stored procedure. I need something on these lines:
set @productCount = exec procGetCount
I did not manage to get this to work. I am using Microsoft Sql Server 7 and 2000.
Thanks a lot,
Chris
|
|
|
|
|
You can't (in those versions at least) that I know of.
Either you have to make it into a function or use output parameters.
|
|
|
|
|
in sql 2000 sp4(mabye in other versions of 2000 but i dont have any to test on)
EXEC @var = procGetCount
but you have to RETURN the value out of the stored procedure for it to work and sql 2000 can only return int values.
example procedure code
DECLARE @var INTEGER
SET @var = 3
RETURN @var
you can also put SELECT @var before the return if you need to as well.
|
|
|
|
|
I am using SQL Server 2005, I have some select and update statements in my query with WHERE clause
I want to prevent these queries from SQL injection attacks.
What are the steps and precautions to be taken for SQL Injection attacks?
Does anybody have suggestions?
Thanks in advance,
|
|
|
|
|
i'm not entirely sure of all of them but dont let the user add things to the where clause with a textbox..but sometimes thats unavoidable, in those cases you should be sure to scrub their input of "special" characters (hopefully you dotn need them to do what you're doing) "special" characters are things like ' " ; () etc.. non alpha numeric characters, i usually allow ' to let people use names like O'Malley but if you're adding that to a string you'lll have to go through and double them i.e. turn each ' into '' or sql server will throw an unterminted string literal or missing ' error. i dotn know if thats all you have to do but it should make it vastly more difficult for anyone trying a sql injection attack. I use RegEx's to do some of the validation on the users' input.
|
|
|
|
|
I have 2 queries,
say one for selecting records from a table based on some criteria say product and availability like this:
Select * from Stock where product = 'cooler' and availability = 'Available'
and another when the product is not available change the availability with update statement in my query like this:
UPDATE Stock SET availability = 'not available' WHERE StockId ='1' and product = 'cooler'
Now I want to prevent these queries from SQL injection attacks,
From the following links I came to know that if I use parameter collection in ASP.NET 2.0 then it is SQL Injection safe:
http://davidhayden.com/blog/dave/archive/2004/03/06/172.aspx[^]
http://forums.asp.net/p/1232362/2227463.aspx[^]
but do we have some other methods to prevent my above queries????
|
|
|
|
|
using a select * in code always a bad idea, it opens up the possibility of problems if column order changes or a new column is added, mostly this occurs with selecting * from views. A parameter list should help, but i dont like them. I just clean the user input, which is a good practice regardless of what method you use to construct the query to send to the database.
|
|
|
|
|
Besides being a bit off topic, then select * is not inherently bad. It most certainly have uses, and the main reason to *not* use SELECT * is to minimize network traffic. No need to move data you do not need.
|
|
|
|
|
unless you are selecting from a view which is used in several places, and an update to one place requires a change to the view, you could break anywhere where you used select *, but select col1, col2, col3 wont break as long as those columns are still present in the view. it not only needs to work well now it needs to work well in the future and not cause unnecessary problems for other people who may be doing updates, and not know all sections of a project like the person who first wrote them. Good practices will protect you from a lot including security threats, doubling tick marks ' to '', removing special characters, and validating your user's input will prevent more problems than i can count including sql injection, and it doesnt take much longer to write. sorry for getting off topic.
|
|
|
|
|
SELECT col1, col2, col3 will break just as easy as SELECT * if you remove the columns your query depends on.
It has nothing to do with SELECT * or not.
SELECT * is bad if you move (way) more data then you need. It is needless to do. But if you need all - or almost all - the data, it is fine.
|
|
|
|
|
Using parameterized stored procedures should cover you against the most direct forms of SQL Injection attack, unless you use string execution like:
EXEC ('SELECT * FROM MYTABLE WHERE ID = ' @INPUTSTRING)
In that case, validate input and replace ' with '' and do not allow the keywords which could do harm.
Generally - it is a bad thing to take user input and run it directly in a query, but as said - if you use parameterized SPROCs you should be pretty safe to begin with.
|
|
|
|
|
Actually I am trying to implementa function which can restrict or blacklist some database syntaxes like select, sys, insert etc. and do not allow them to fire i.e. to check input ...
But yet I m not entirely successful in that...
No idea how to implement that I just thought this thing yet...
|
|
|
|
|
Hi!
Let's say this is an old web site built in ASP with VBScript and it's just had an SQL Injection attack (as happened to me a week ago - I have hundreds of old sites running, and sometimes I or a colleague have had a bad day years ago - it tends to come back and bite you). You've stopped the web, cleaned the database (let's say it only appended script tags to some text fields, so it was a quick fix - this is common bot behaviour).
Quick and dirty fix to get it up:
Search for the word "WHERE" in the site source. Replace this:
Recs.Open "SELECT ... FROM ... WHERE x = '" & something & "' ..." ...
with:
Recs.Open "SELECT ... FROM ... WHERE x = '" & Replace(something, "'", "''") & "' ..." ...
and this:
Recs.Open "SELECT ... FROM ... WHERE someid = " & something & " ...", ...
with:
Recs.Open "SELECT ... FROM ... WHERE someid = " & CLng(something) & " ...", ...
'CLng will throw on invalid input!
This is a naive fix and only stops the most common attacks. So... when the site is up, you spend the next day rewriting further (from memory, possibly lots of syntax errors and remembering things wrong, but the concept is correct):
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = "SELECT ... FROM ... WHERE x = ? ..."
cmd.Parameters.Add cmd.CreateParameter("@p1", ...)
Recs.Open cmd, ...
And yes, you'd want to wrap that in a function, like so:
Recs.Open SqlCmd("SELECT...", Array(param1, param2)), ...
(the hideous Array() construct is because VBScript doesn't know about parameter arrays - also beware that you must analyze the values to figure out the proper types and other parameter metadata in the SqlCmd function)
If you're using .Net, look at the SqlCommand (or DbCommand) object. Same thing. There are several reasons to use parameterized queries:
- SQL Server can cache execution plans for similar queries - this improves performance A LOT if you do the same query over and over with different parameters.
- You're immune to SQL Injection attacks.
- You're code gets easier to read (provided you encapsulate the mechanism properly, otherwise it will bloat the code).
Then again, why don't we just round up all hackers and shoot them? I could even consider molesting them for a while first... 
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
Currently i m using sql server 2000. when i directly modify the values inside the database the following TRIGGER fires correctly. But when pass values from front end (ASP.NET with C#, .net 2005, 2.0 framework) "update tbl_ORA_TBOps set Percentage_Compliant=((@comp/@tot_ex)*100) where AutoID=@id" this alone not working.. what problem.....? plz..
CREATE TRIGGER [calc] ON dbo.tbl_ORA_TBOps
AFTER INSERT, UPDATE
AS
begin
update tbl_ORA_TBOps set Total=(Compliant+Non_Compliant+InProgress+InputNotReceived+NA)
update tbl_ORA_TBOps set Total_Excl_NA=Total-NA
end
begin
declare @tot numeric
declare @na numeric
declare @tot_ex numeric
declare @comp numeric
declare @id numeric
select @tot=(Select total from inserted)
select @na=(Select NA from inserted)
select @tot_ex=(Select Total_Excl_NA from inserted)
select @comp=(Select Compliant from inserted)
select @id=(Select AutoID from inserted)
if (@tot=@na)
begin
update tbl_ORA_TBOps set Percentage_Compliant=100 where AutoID=@id
end
else
if (@tot_ex=0)
begin
update tbl_ORA_TBOps set Percentage_Compliant=0 where AutoID=@id
end
else
begin
update tbl_ORA_TBOps set Percentage_Compliant=((@comp/@tot_ex)*100) where AutoID=@id
end
end
in the above trigger, i m getting incorrect Percentage_Compliant value. how to solve this (i m very beginner to TRIGGERS). is the IF...ELSE loop correct? HELP ME! - KARAN
modified on Thursday, May 29, 2008 8:38 AM
|
|
|
|
|
Without looking in detail at your code, here is one critical heads-up about triggers: The inserted and deleted virtual tables contain more than one row. If you run one update statement that affects ten rows, they will contain ten rows each. The trigger gets called once per update statement, not once per row! Similar for all types of triggers.
So... your set @variable = (select ... from ...) would not do at all what you expect it to.
Rethink your approach accordingly.
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
Thanks peter.... I m a beginner to TRIGGERS. so i will rectify my errors. but the thing is here... the ELSE part alone is not working.... other if loops working fine..... could u guide me to proceed this process? when i try the same in C#.NET the loop is working fine...
help me!
thanks for understanding....
|
|
|
|
|
I'm not sure I understand your question. When you're saying loop, what do you mean? You usually don't do work using loops in SQL, as SQL works on sets, not items. With sets, you specify a statement that says what should be done with the entire set. With items (as in C# or another procedural language) you loop over all the elements and do something to them one at a time. These are different ways of thinking altogether, and you can't mix the two.
So, instead of (abstract example, has nothing to do with your sample as I don't understand it, but the thinking should be similar):
-- The following wont work at all if inserted contains
-- more than one row (which it will if the statement
-- firing the trigger affects more than one row):
set @x = (select x from inserted)
if @x = 10 then set @y = 20 else set @y = 30
update target set y = @y
you do something like this:
update target set y = (case x when 10 then 20 else 30 end)
where target.id in (select id from inserted)
Note: There is an example that looks like yours in SQL Server Books Online, but like yours, it will fail when an update or insert statement affects more than one row.
I can't elaborate much on this without writing a small book, but what you have to realize is that with SQL you have to think quite differently than in procedural languages.
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
|
I want to know where all a particular tables primary key is used as foriegn key in the database. Is there any built in stored procedure for that?
|
|
|
|
|
You haven't provided the database version you're using then you can look up "Information Schema Views" in BOL. The following query makes use of the information schema which is SQL-92 compliant and should work with any database engine which is also SQL-92 compliant, not just SQL Server:
<br />
<br />
SELECT P.TABLE_NAME AS ParentTable, P.CONSTRAINT_NAME AS PrimaryKey, FK.TABLE_NAME AS ChildTable, FK.CONSTRAINT_NAME AS ForeignKey <br />
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS P<br />
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.UNIQUE_CONSTRAINT_NAME = P.CONSTRAINT_NAME<br />
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME<br />
WHERE P.CONSTRAINT_TYPE = 'PRIMARY KEY'<br />
ORDER BY ParentTable, ChildTable<br />
<br />
|
|
|
|
|
Hi to all
i want to copy entire data from one of my table to that of another
table. source table is created and having 1000 of rows and
destination table is not created this i want to do in a single statment.
Thanks in advance.
|
|
|
|
|
soniasan wrote: i want to copy entire data from one of my table to that of another
table. source table is created and having 1000 of rows and
destination table is not created this i want to do in a single statment.
You will need to look up the SELECT INTO SQL statement.
|
|
|
|
|
Be warned, if there are large volumes of data using the SELECT INTO method could lock all other processes out as it locks the system tables (such as syscolumns, sysobjects etc) for the duration of the select.
It is much better to do it in two steps:
SELECT * INTO new_table FROM old_table WHERE 1 = 2 -- creates the new table
INSERT INTO new_table SELECT * FROM old_table -- populate it
Bob
Ashfield Consultants Ltd
|
|
|
|
|
So flame me for asking something which is probably Google-able, but I couldn't find it!
What is the maximum string-length that can be output using the PRINT command in SQL 2005?
E.G
<br />
DECLARE @myString Varchar(max)<br />
<br />
SET @myString = 'Imagine a very looooooooong string here!'<br />
<br />
PRINT @myString
|
|
|
|
|
It was easy to find out - 8000
declare @a varchar(max)
select @a = replicate('a',7999) + 'b'
print @a
--prints 7999 a's and a b
select @a = replicate('x',8000) + 't'
print @a
--prints 8000 x's but no t
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
