防止在Oracle中进行SQL注入 [英] Prevent SQL injection in oracle

问题描述

首先，我尝试使用以下代码

firstly i tried using following code

strQuery = @"SELECT PASSWORD FROM IBK_USERS where upper(user_id) =upper('" + UserPrefix + "')";

try
{
    ocommand = new OracleCommand();
    if (db.GetConnection().State == ConnectionState.Open)
    {
        ocommand.CommandText = strQuery;
        ocommand.Connection = db.GetConnection();                       
        odatareader = ocommand.ExecuteReader();
        odatareader.Read();

最后我在上面的查询中进行了转换，以防止这样的sql注入

and finally i converted above query to prevent sql injection like that

strQuery = @"SELECT PASSWORD FROM IBK_USERS where upper(user_id) =upper(:UserPrefix)";
try
{
    ocommand = new OracleCommand();
    if (db.GetConnection().State == ConnectionState.Open)
    {
        ocommand.CommandText = strQuery;
        ocommand.Connection = db.GetConnection();

        ocommand.Parameters.Add(":UserPrefix",OracleDbType.Varchar2,ParameterDirection.Input);
        ocommand.Parameters[":UserPrefix"].Value = UserPrefix;
        odatareader = ocommand.ExecuteReader();

但这不起作用

推荐答案

删除参数名称前面的冒号.因此，而不是...

Remove the colon in front of the parameter name. So instead of...

ocommand.Parameters.Add(":UserPrefix", ...);

...执行此操作...

...do this...

ocommand.Parameters.Add("UserPrefix", ...);

Ditto用于设置参数值.

Ditto for setting parameter value.

与您的问题没有直接关系:在WHERE子句中使用upper(user_id)可能需要基于函数的索引才能有效运行.另外，user_id真的需要是一个字符串吗?-您不能仅将其设为整数吗?

Not directly related to your question: using upper(user_id) in the WHERE clause may require a function-based index to run efficiently. Also, does the user_id really need to be a string - can't you just make it integer?

这篇关于防止在Oracle中进行SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！