如何使用占位符将列名值作为SQL参数传递 [英] How to pass a column name value as SQL argument using placeholders

问题描述

如何使用参数占位符将列名值作为SQL参数传递?

How to pass a column name value as a SQL argument using parameter placeholders ?

目标是要实现这一目标:

The goal is having this working:

var sql = "SELECT * FROM Condos WHERE @0 LIKE @1";
var sqlData = db.Query(sql,choice,"%"+searchString+"%");

choice是将存储column name

@0是column name(并且我没有成功将其作为参数传递)

@0 is a column name (and I don't succeed to pass it as an argument)

@1是search string(我对此没有问题)

@1 is a search string (and I have no problem with it)

已经阅读并尝试了很多东西:

Have read and tried a lot of things:

下面没有抛出任何错误但没有带来任何数据

Below one doesn't throw any error but doesn't bring any data

var sql = "SELECT * FROM Condos WHERE @0 LIKE @1";
var sqlData = db.Query(sql,choice,"%"+searchString+"%");

以下一掷Column name not valid = '@0'

var sql = "SELECT * FROM Condos WHERE [@0] LIKE @1"
var sqlData = db.Query(sql,choice,"%"+searchString+"%");

以下一掷Column name not valid = 'choice'

var sql "SELECT * FROM Condos WHERE choice LIKE @0");
var sqlData = db.Query(sql,"%"+searchString+"%");

以下一掷Must declare scalar variable "@choice"

var sql "SELECT * FROM Condos WHERE @choice LIKE @0");
var sqlData = db.Query(sql,"%"+searchString+"%");

下面没有抛出任何错误但没有带来任何数据

Below one doesn't throw any error but doesn't bring any data

var sql = "SELECT * FROM Condos WHERE '@choice' LIKE @0";
var sqlData = db.Query(sql,"%"+searchString+"%");

下面没有抛出任何错误但没有带来任何数据

Below one doesn't throw any error but doesn't bring any data

var sql = "SELECT * FROM Condos WHERE '@choice' LIKE @0";
var sqlData = db.Query(sql,"%"+searchString+"%");

下面没有抛出任何错误但没有带来任何数据

Below one doesn't throw any error but doesn't bring any data

var sql = "SELECT * FROM Condos WHERE '"+choice+"' LIKE @0";
var sqlData = db.Query(sql,"%"+searchString+"%");

以下之一:大崩溃

var sql = "SELECT * FROM Condos WHERE '"+@choice+"' LIKE @0";
var sqlData = db.Query(sql,"%"+searchString+"%");

以下一掷Column name not valid = 'NameShort'

这正是正确的列名

var sql = "SELECT * FROM Condos WHERE ['"+choice+"'] LIKE @0";
var sqlData = db.Query(sql,"%"+searchString+"%");

下一个:大崩溃

var sql = "SELECT * FROM Condos WHERE ['"+@choice+"'] LIKE @0";
var sqlData = db.Query(sql,"%"+searchString+"%");

帮助!!!!!!!!!!!!!!

推荐答案

最简单的答案是您不能这样做.仅值支持参数，列名不支持.

The short answer is that you can't; parameters are only supported for values, not for column names.

您必须像Richard所说的那样依靠直接文本插入(无论是在代码中发生还是通过使用SQL Server exec()函数发生)，或者使用某种库(如LINQ)允许您可以动态构造查询，然后将其转换为文本表示形式.

You either have to fall back on direct text insertion as Richard has said (whether this happens in code or via the use of the SQL Server exec() function), or use some kind of a library (like LINQ) that lets you construct queries dynamically and then converts that into a text representation.

如果您使用直接文本插入路线，请非常确保您不允许插入直接用户输入；自己进行某种翻译，以避免SQL注入攻击.

If you go the direct text insertion route, be very sure that you are not allowing direct user input to be inserted; do some kind of translation yourself to avoid SQL Injection attacks.

这篇关于如何使用占位符将列名值作为SQL参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！