由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Database版 - stored procedure help Please.....
相关主题
Why does this Stored procedure fail ?请教一个SQL的问题
请各位帮我看看这个最简单的Stored Procedure (转载)Help: SAS
#在SQL里啥子意思Recompile SQL Server Store procedure
stored procedure running 很慢的问题SQL debug step into a store procedure from another one (转载)
求教: dba privilageStored Procedure?
T-SQL 问题如何分析Stored Procedure运行时间过长?
pass parameter from stored procedure to SSIShow can i execute pl/sql (or stored procedure) in java??
sql的2个问题 (转载)[转载] intensive I/O and odbc problem
相关话题的讨论汇总
话题: sql话题: stored话题: title话题: procedure话题: date
进入Database版参与讨论
1 (共1页)
j****l
发帖数: 6
1
I have the following sql command needs to transfer to stored procedure:
select * from book where title " + sTtile + " and author " + sAuthor + " and
date " + sDate;
sTitle and the other three are string. sTitle could be 'is not null' or "like
'%' + @Title +'%' ". It's the same thing for sAuthor and sDate.
I tried to write stored procedue of
... where title @title and author @author and date @date
but it failed.
The purpose of this query is to execute multiple-parameter query.
Your help or sugges
s***m
发帖数: 28
2
What database you are using?

"like

【在 j****l 的大作中提到】
: I have the following sql command needs to transfer to stored procedure:
: select * from book where title " + sTtile + " and author " + sAuthor + " and
: date " + sDate;
: sTitle and the other three are string. sTitle could be 'is not null' or "like
: '%' + @Title +'%' ". It's the same thing for sAuthor and sDate.
: I tried to write stored procedue of
: ... where title @title and author @author and date @date
: but it failed.
: The purpose of this query is to execute multiple-parameter query.
: Your help or sugges

d****a
发帖数: 6
3
if in SQL Server, try:
create proc test
@title varchar(50),
@author varchar(50),
@date varchar(50)
as
declare @sql varchar(200)
set @sql = 'select * from book'
if @title = 'is not null'
set @sql = @sql + ' where title is not null'
else
set @sql = @sql + ' where title like ''%' + @title + '%'''
if @author = 'is not null'
set @sql = @sql + ' and author is not null'
else
set @sql = @sql + ' and author like ''%' + @author + '%'''
if @date = 'is not null'
set @

【在 j****l 的大作中提到】
: I have the following sql command needs to transfer to stored procedure:
: select * from book where title " + sTtile + " and author " + sAuthor + " and
: date " + sDate;
: sTitle and the other three are string. sTitle could be 'is not null' or "like
: '%' + @Title +'%' ". It's the same thing for sAuthor and sDate.
: I tried to write stored procedue of
: ... where title @title and author @author and date @date
: but it failed.
: The purpose of this query is to execute multiple-parameter query.
: Your help or sugges

j****l
发帖数: 6
4
Thank you very much. The stored procedure works.
But my main problem is that I am using this stored procedure in ASP.NET.
My goal for database access is that only ASPNET account can execute the stored
procedure. NO other permission allowed in this web-database connection. When I
use your stored procedure and mine, the select permission of book have to be
granted which is not what I want.
I am implementing a mulitple parameter query on the web with three text
fields. User can search by one parame

【在 d****a 的大作中提到】
: if in SQL Server, try:
: create proc test
: @title varchar(50),
: @author varchar(50),
: @date varchar(50)
: as
: declare @sql varchar(200)
: set @sql = 'select * from book'
: if @title = 'is not null'
: set @sql = @sql + ' where title is not null'

aw
发帖数: 127
5
i had the same problem before and i need to pass 10+ optional parameters(and
all combinations) to the sp. i asked our dba and he said using a string
variable (as DaaDaa's) is the only way he knows. i doubt doing this may lose
the most benefits we can get from sp because the SQL string is not checked and
validated at compile time. another major problem is this code may be hacked.
for example, if the user enters "09/11/2002 UNION select * from users" (only
an example), he may get illegal access to

【在 j****l 的大作中提到】
: Thank you very much. The stored procedure works.
: But my main problem is that I am using this stored procedure in ASP.NET.
: My goal for database access is that only ASPNET account can execute the stored
: procedure. NO other permission allowed in this web-database connection. When I
: use your stored procedure and mine, the select permission of book have to be
: granted which is not what I want.
: I am implementing a mulitple parameter query on the web with three text
: fields. User can search by one parame

j****l
发帖数: 6
6
I thought of this also. My solution is let the last parameter be a dropdown
list and the default value of it is "is not null" in sp. SO people cannot add
extra select, or drop statement in the query. Is that a good enough solution?
I heard about sql injection hacking. But I am not good at it. If you have any
better idea, please inform me. Please...
Thanks a lot!
Jadell

and
connect
When
be

【在 aw 的大作中提到】
: i had the same problem before and i need to pass 10+ optional parameters(and
: all combinations) to the sp. i asked our dba and he said using a string
: variable (as DaaDaa's) is the only way he knows. i doubt doing this may lose
: the most benefits we can get from sp because the SQL string is not checked and
: validated at compile time. another major problem is this code may be hacked.
: for example, if the user enters "09/11/2002 UNION select * from users" (only
: an example), he may get illegal access to

aw
发帖数: 127
7
it doesn't solve the problem, the user can enter "UNION select * from users --
" to bypass it (the date parameter will be treated as comment and not
executed).
i have no good idea to solve it unless you write some utility functions to
check/filter the user inputs for SQL sensitive key words, but don't know how
much safer it could be.
googled and found this artical:
http://www.nextgenss.com/papers/advanced_sql_injection.pdf

add
solution?
any
parameters(and
lose
hacked.
(only
to

【在 j****l 的大作中提到】
: I thought of this also. My solution is let the last parameter be a dropdown
: list and the default value of it is "is not null" in sp. SO people cannot add
: extra select, or drop statement in the query. Is that a good enough solution?
: I heard about sql injection hacking. But I am not good at it. If you have any
: better idea, please inform me. Please...
: Thanks a lot!
: Jadell
:
: and
: connect

j****l
发帖数: 6
8
Thanks, I'll take a look at the pdf file.
My real application is the last parameter's web form is a drop down list which
means people can not enter anything other than my predefined values.
Any idea?
Thanks again!

【在 aw 的大作中提到】
: it doesn't solve the problem, the user can enter "UNION select * from users --
: " to bypass it (the date parameter will be treated as comment and not
: executed).
: i have no good idea to solve it unless you write some utility functions to
: check/filter the user inputs for SQL sensitive key words, but don't know how
: much safer it could be.
: googled and found this artical:
: http://www.nextgenss.com/papers/advanced_sql_injection.pdf
:
: add

aw
发帖数: 127
9
just as i said before, it doesn't solve the problem, the order of your
parameters doesn't matter, let me try to make it clearer:
for exmaple, you have 3 values, name, title, and date. the user entered
"john", "UNION select * from users -- ", and then select the date from your
dropdown list.
the query will look like:
SELECT * from WHERE name = 'john' UNION select * from users --
AND date = .
see the problem? the "AND date=" will not be executed and the user gets
access to

【在 j****l 的大作中提到】
: Thanks, I'll take a look at the pdf file.
: My real application is the last parameter's web form is a drop down list which
: means people can not enter anything other than my predefined values.
: Any idea?
: Thanks again!

1 (共1页)
进入Database版参与讨论
相关主题
[转载] intensive I/O and odbc problem求教: dba privilage
stored procedure 菜鸟一问, PLEASE HELP,在线等T-SQL 问题
Stored Procedure / Trigger - 谢谢pass parameter from stored procedure to SSIS
怎末优化一个stored procedure ?sql的2个问题 (转载)
Why does this Stored procedure fail ?请教一个SQL的问题
请各位帮我看看这个最简单的Stored Procedure (转载)Help: SAS
#在SQL里啥子意思Recompile SQL Server Store procedure
stored procedure running 很慢的问题SQL debug step into a store procedure from another one (转载)
相关话题的讨论汇总
话题: sql话题: stored话题: title话题: procedure话题: date