经典ASP页面上sql查询中的条件语句 [英] Conditional statement in sql query on classic ASP Page

问题描述

我正在名为123.asp的页面中获取三个值(123.asp?School = NewEnglishSchool& Batch = 1& AcademicYear = 2)

I am fetching three values in my page named 123.asp (123.asp?School=NewEnglishSchool&Batch=1&AcademicYear=2)

School = Request.QueryString("School")
Batch = Request.QueryString("Batch")
AcademicYear = Request.QueryString("AcademicYear")

我有一个简单的查询，形式如下strSqlData =从MyTable中选择*，其中性别=男性".

I have a simple query in form like below strSqlData="select * from MyTable where gender="male""

如果我通过 Request.QueryString表示(123.asp?Batch = 1& AcademicYear = 2)页面上的School，找到了Batch和AcademicYear的值，则我需要创建如下查询:

然后我要编写一个类似这样的查询

then I want to write a query something like this

strSqlData="select * from MyTable where gender="male" 
if len(AcademicYear)<>o then 
and AcademicYear='"&AcademicYear&"'

elseif len(Batch)<>o then 
and Batch='"&Batch&"'
end if  

elseif len(School)<>o then 
and School='"&School&"'
end if  "

我想用适当的方法创建上述内容，但不使用大小写，因为页面上有很多过滤器，例如学校，批次和学年.

I want to create something like above but in proper method without using case, because I have so many filters on the page like school, batch and Academicyear.

推荐答案

在VBScript中，您可以使用&符号来连接字符串.试试这个:

In VBScript you can use the ampersand (&) symbol to concatenate strings. Try this:

strSqlData="select * from MyTable where gender='male'" 

if AcademicYear <> "" then 
strSqlData= strSqlData & " and AcademicYear=" & AcademicYear
end if

if Batch <> "" then
strSqlData= strSqlData & " and Batch=" & Batch    
end if  

if School <> "" then 
strSqlData= strSqlData & " and School=" & School
end if  

您有三个单独的和"子句附加到您的sql查询中.条件语句彼此独立，因此您不应该在单个条件语句中使用 elseif 表示不同选项的情况.如果stringname<>字符串不为空，则检查字符串是否为空更简单." 比使用 len ，(而且我怀疑您的条件语句会起作用，因为您似乎在使用小写字母"o"，而应该使用零))

You have three separate "and" clauses to append to your sql query. The conditional statements are independent of each other so you shouldn't be using elseif which is for different options within a single conditional statement. It's simpler to check whether or not a string isn't empty if stringname <> "" than using len, (and I doubt your conditional statements would work because you appear to be using a lower case letter "o" where you should be using a zero)

在组装这样的sql查询时很容易出错.在测试中，通常值得添加类似 Response.Write strSqlData 的行，然后再尝试执行以检查查询是否符合您的意图

It's very easy to make mistakes when you're assembling sql queries like this. In testing it's often worth adding a line like Response.Write strSqlData before you try to execute it to check that the query is what you were intending

但是，正如其他注释所建议的那样，您的代码容易受到SQL注入攻击的攻击.包含".asp"的网址几乎可以保证迟早会遭到ASPROX类型的攻击.参数化查询是防止这种情况的最佳方法，但是如果您的查询字符串值都是数字，则一种快速方法是使用 cint()-例如

As other comments have suggested though, your code is vulnerable to an SQL injection attack. Urls which contain ".asp?" can almost be guaranteed to be hit by an ASPROX type attack sooner or later. Parameterised queries are the best means of guarding against this, but one quick approach if your querystring values are all numeric is to use cint() - eg

strSqlData= strSqlData & " and AcademicYear=" & cint(AcademicYear)

如果查询字符串包含数字以外的任何内容，这将引发类型不匹配错误，并且脚本将在尝试执行sql查询之前崩溃.

This will throw a type mismatch error if the querystring contains anything other than numbers, and the script will fall over before it attempts to execute your sql query.

这篇关于经典ASP页面上sql查询中的条件语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！