stored procedure help Please..... - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

stored procedure help Please.....

stored procedure help Please.....# Database - 数据库

j*l2003-09-08 07:09

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*m2003-09-08 07:09

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*a2003-09-08 07:09

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 的大作中提到】

j*l2003-09-08 07:09

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'

aw2003-09-08 07:09

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*l2003-09-08 07:09

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

aw2003-09-08 07:09

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*l2003-09-08 07:09

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

aw2003-09-08 07:09

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!