Parameterized query
-
Can I use a Parameter ( either in MSAccess / SQL Server ) to set the name of a table which I want to create or get data from . To make it simple, can I execute an SQL this way, (Assuming the DB to be MSAccess) Parameters TableName Text; SELECT * FROM TableName; Or is there a workaround for this?
-
Can I use a Parameter ( either in MSAccess / SQL Server ) to set the name of a table which I want to create or get data from . To make it simple, can I execute an SQL this way, (Assuming the DB to be MSAccess) Parameters TableName Text; SELECT * FROM TableName; Or is there a workaround for this?
You for something like that you will need to create dynamic SQL in either .NET or in a Stored Procedure. In other words:
//C#
string tableName = "Account";
string sqlQuery = string.Format("SELECT * FROM {0}", tableName);Here's how you do this in a stored procedure
CREATE PROCEDURE dbo.ExecQuery
(
@tableName varchar(50)
)
AS
declare @sql varchar(100)
set @sql = 'SELECT * FROM ' + @tableName
exec @sqlreturn 0
The main question is, is this there a different approach all together? ~Javier Lozano
-
You for something like that you will need to create dynamic SQL in either .NET or in a Stored Procedure. In other words:
//C#
string tableName = "Account";
string sqlQuery = string.Format("SELECT * FROM {0}", tableName);Here's how you do this in a stored procedure
CREATE PROCEDURE dbo.ExecQuery
(
@tableName varchar(50)
)
AS
declare @sql varchar(100)
set @sql = 'SELECT * FROM ' + @tableName
exec @sqlreturn 0
The main question is, is this there a different approach all together? ~Javier Lozano
Be careful with both those approaches as they are weak points from which a SQL Injection attack could be launched. If you are selecting an existing table, then it is worthwhile checking that the table exists already with a proper parameterised query so you can be sure there isn't anything nasty injected into the tableName variable you are using.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Be careful with both those approaches as they are weak points from which a SQL Injection attack could be launched. If you are selecting an existing table, then it is worthwhile checking that the table exists already with a proper parameterised query so you can be sure there isn't anything nasty injected into the tableName variable you are using.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
You are very much correct, and why I specifically asked for a parameterised query is for the same reason, to avoid Injection Attack. So can you explain how I can use a parameter to substitite for table name? I couldn't get it to work !!
csylesh wrote: So can you explain how I can use a parameter to substitite for table name? I couldn't get it to work !! You would have to use something like Javier suggested. You cannot have a table name as a parameter. However, table names are unlikely to have strange characters in them* so you can easily check it for non-alphanumeric characters and reject the name if it contains such things. Also, if you are just checking for a tables existance, you can create a query such as
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @MyTableNameParameterDoes this help? * Having said that, a table name, if I remember correctly, can be any unicode string upto 128 characters so it is still possible.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
csylesh wrote: So can you explain how I can use a parameter to substitite for table name? I couldn't get it to work !! You would have to use something like Javier suggested. You cannot have a table name as a parameter. However, table names are unlikely to have strange characters in them* so you can easily check it for non-alphanumeric characters and reject the name if it contains such things. Also, if you are just checking for a tables existance, you can create a query such as
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @MyTableNameParameterDoes this help? * Having said that, a table name, if I remember correctly, can be any unicode string upto 128 characters so it is still possible.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Thanks, I just wanted to hear from somebody authoritatively that a parameter substitution is not possible for a table name. Let me raise a new question for curiosity, how does parameter substitution actually prevents injection attack evenif it is a string datatype that is being substituted. Is it some magic with the quotes substitution or the like? Any ideas?? Thanks in advance.