使用命令参数的Web API没有返回任何数据 [英] Web API using command parameters is not returning any data

问题描述

该网站的API查询接收字符串数组作为输入参数的Oracle数据库。我试图使用的命令参数，以避免SQL注入，但下面的代码不会引发任何错误，但没有给出结果。

The web API to query the oracle database which receives the array of strings as an input parameter. I am trying to use the command parameters to avoid the SqL injection, but the below code does not throw any error but does not give the result.

public class PDataController : ApiController
{
    public HttpResponseMessage Getdetails([FromUri] string[] id)
    {

        List<OracleParameter> prms = new List<OracleParameter>();
        string connStr = ConfigurationManager.ConnectionStrings["PDataConn"].ConnectionString;
        using (OracleConnection dbconn = new OracleConnection(connStr))
        {
            var inconditions = id.Distinct().ToArray();
            var srtcon = string.Join(",", inconditions);
            DataSet userDataset = new Dataset();
            var strQuer = @"SELECT STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY,  
            STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER,  
            Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, 
            STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME  
            FROM STCD_PRIO_CATEGORY_DESCR 
            WHERE STCD_PRIO_CATEGORY_DESCR.STD_REF(";
            StringBuilder sb = new StringBuilder(strQuery);
            for(int x = 0; x < inconditions.Length; x++)
            {
                sb.Append(":p" + x + ",");
                OracleParameter p = new OracleParameter(":p" + x,OracleDbType.NVarchar2 );
                p.Value = inconditions[x];
                prms.Add(p);
            }
            if(sb.Length > 0) sb.Length--;
            strQuery = strQuery + sb.ToString() + ")";
            using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
            {
                 selectCommand.Parameters.AddRange(prms.ToArray());
                 using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
                {
                    DataTable selectResults = new DataTable();
                    adapter.Fill(selectResults);
                    var returnObject = new { data = selectResults };
                    var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
                    ContentDispositionHeaderValue contentDisposition = null;
                    if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
                    {
                        response.Content.Headers.ContentDisposition = contentDisposition;
                    }
                    return response;
                }
            }

        }
    }
}

下面是同时的SelectCommand的CommandText中调试什么我正在

Below is what I am getting while debugging in commandText of selectCommand

"SELECT \r\n STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, \r\n 
 STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, \r\n 
 Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, \r\n  
 STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME  \r\n  
 FROM \r\n 
 STCD_PRIO_CATEGORY_DESCR \r\n 
 WHERE \r\n 
 STCD_PRIO_CATEGORY_DESCR.STD_REF IN(SELECT \r\n 
 STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, \r\n 
 STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, \r\n 
 Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, \r\n  
 STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME  \r\n  
 FROM \r\n 
 STCD_PRIO_CATEGORY_DESCR \r\n 
 WHERE \r\n 
 STCD_PRIO_CATEGORY_DESCR.STD_REF IN(:p0)"

由于我目前给

strQuery = strQuery+ sb.ToString() + ")";

的

选择正在重演。但如果我只是给

The select is being repeated. But if I just give

 strQuery = sb.ToString() + ")";

鉴于strQuery而调试

Whereas the strQuery while debugging is

SELECT \r\n STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, \r\n 
STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, \r\n 
Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, \r\n  
STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME  \r\n  
FROM \r\n 
STCD_PRIO_CATEGORY_DESCR \r\n 
WHERE \r\n 
STCD_PRIO_CATEGORY_DESCR.STD_REF IN(:p0)

我得到的回报是

{"data":[]}

我应该附上P0在''因为我们接收输入的字符串数组。

Should I enclose p0 in '' because the input we receive is array of Strings.

但是当我尝试在SQL开发我收到的记录相同的ID。任何帮助是极大的赞赏。

But the same ID when i try in the SQL developer I am getting the records. any help is greatly appreciated.

推荐答案

这与OP聊天事实证明，在OP添加围绕阵列参数单引号ID。以这种方式设置格式的查询字符串收到其中的值

From the chat with the OP it turned out that the OP added single quotes around the array parameter ID. The values where received from a query string formatted in this way

http:// localhost:80/api/PData?id='JW217T_01'

，这是传递一个字符串作为参数值的一种尝试。

and this was an attempt to pass a string as the parameter value.

然而，如果你使用参数，并指定其数据类型（NVARCHAR2），那么数据库引擎知道有足够的了解价值本身做正确的引用，因此对于参数的值应该不会有他们周围的单引号。

However if you use a parameter and specify its datatype (NVarChar2) then the database engine knows enough about the value to do the correct quoting by itself, so the values for the parameters should not have single quotes around them.

更改查询字符串的格式

http:// localhost:80/api/PData?id=JW217T_01

解决了这一问题。

这篇关于使用命令参数的Web API没有返回任何数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！