having an issue with sqLite param queries not working as expected

Question

So can someone tell me why the commented out works while last two lines does not? I am returning a row from a sqLite database using compact framework c#..

            //String sqlcomm = "SELECT * FROM Asset WHERE " + assetColName + " = \'" + assetColValue + "\'";
            //command.CommandText = sqlcomm;
            command.CommandText = @"SELECT * FROM Asset WHERE $assetColName = '$assetColValue'";
            command.Parameters.AddWithValue("$assetColName", assetColName);
            command.Parameters.AddWithValue("$assetColValue", assetColValue);

score 1 · Answer 1 · answered Sep 25 '13 at 00:13

1

Generally you can't parameterize column names. Try building the command text with the column name put in as per your commented out line but still with the parameter for the column value.

answered Sep 25 '13 at 00:13

dejester

616
4
6

score 0 · Accepted Answer · answered Sep 25 '13 at 15:17

I have figured out a way to include a work around for parametrized column names. I came up with a different way and since I would be the only one using the column names then I believe this is still a safe bet.

            String sqlcomm = "SELECT * FROM Asset WHERE " + assetColName + " = ";
            command.CommandText = sqlcomm + "$assetColValue";

            //command.CommandText = @"SELECT * FROM Asset WHERE $assetColName = '$assetColValue'";
            //command.Parameters.AddWithValue("$assetColName", assetColName);

            command.Parameters.AddWithValue("$assetColValue", assetColValue);

As you can see from the code above. I then concatenated strings together and was able to use my parametrized column name and value which then the value is securely added. The column name however is not secured but this is a method that only I will be using so its still somewhat safe. I could add regular expressions if I want to be more secure but you get the idea of the fix.

Does anyone foresee a problem with this?

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

You can always check if column exist using INFORMATION_SCHEMA table.

Usages of INFORMATION_SCHEMA: http://blog.sqlauthority.com/2011/10/02/sql-server-ce-list-of-information_schema-system-tables/

TSQL Example: How to check if a column exists in SQL Server table

edited May 23 '17 at 10:25

Community

1
1

answered Sep 26 '13 at 10:24

dzaba

90
1
7

I'm really not worried about if a column exists. I'm making a general function that will return whatever I want from whichever column i put it. The answer i posted at the bottom does this. – Tyler Buchanan Sep 26 '13 at 17:03

having an issue with sqLite param queries not working as expected

3 Answers3