Skip to content
This repository has been archived by the owner on Oct 13, 2020. It is now read-only.

Parameterized query-syntax with unnamed parameters - Not working with create table #2

Open
gernotpokorny opened this issue Sep 15, 2017 · 0 comments

Comments

@gernotpokorny
Copy link

Description
Parameterized query-syntax with unnamed parameters works with INSERT INTO and SELECT but not with CREATE TABLE.

Prerequisites:

  • C# Console application
  • As stated in the comments -> Nuget-package System.Data.Sqlite + update everything after install.
  • You need a NorthwindTest.sl3 database in the debug-folder.

Error
The section that is commented with "test" fails. I guess it is generally not necessary to support that syntax with CREATE TABLE at the table-name, but is this a bug or as intended, that this syntax cannot be used at the table-name?

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;

// Important: Does use nuget packages: System.Data.SQlite (the official one). Must be downloaded + insert using-statements (using System.Data.SQLite;).

// Important: Always use parameterized query, because of SQL-injection-attacks.

namespace ParameterizedQueryUnnamedParameters
{
    class Program
    {
        static void Main(string[] args)
        {
            var qe = new QueryExeceuter();
        }
    }

    class QueryExeceuter
    {
        const string dbConnectionString = @"Data Source=NorthwindTest.sl3;Version=3;"; // NorthwindTest.sl3 is the path when NorthwindTest.sl3 is in the Debug-folder
        SQLiteConnection sqliteCon;
        const string tableName = "MyTable";
        string query;
        SQLiteCommand sqlCommand;

        public QueryExeceuter()
        {
            sqliteCon = new SQLiteConnection(dbConnectionString);

            // Open connection to database
            try
            {
                sqliteCon.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // Create table if does not exist
            if (!TableExists(tableName))
            {
                query = $@"CREATE TABLE {tableName}(Id INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT, Age INTEGER);";
                sqlCommand = new SQLiteCommand(query, sqliteCon);
                sqlCommand.ExecuteNonQuery();
            }

            // Parameterized query-syntax with unnamed parameters
            query = @"insert into MyTable values ((SELECT max(id) FROM MyTable) + 1, ?, ?, ?); ";
            sqlCommand = new SQLiteCommand(query, sqliteCon);
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = "Bob" });
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = "Johnson" });
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = 24 });
            sqlCommand.ExecuteNonQuery();

            // test
            query = $@"CREATE TABLE ?(Id INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT, Age INTEGER);";
            sqlCommand = new SQLiteCommand(query, sqliteCon);
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = "MyTableLala" });
            sqlCommand.ExecuteNonQuery();

            // Wait for the user to quit the program.
            Console.WriteLine("Press \'q\' to quit the sample.");
            while (Console.Read() != 'q') ;
        }

        bool TableExists(string tableName)
        {
            string query = $@"SELECT * FROM sqlite_master WHERE type='table' AND tbl_name=?;";
            SQLiteCommand sqlCommand = new SQLiteCommand(query, sqliteCon);
            sqlCommand.Parameters.Add(new SQLiteParameter(DbType.String) { Value = tableName });
            SQLiteDataReader reader = sqlCommand.ExecuteReader();
            return reader.Read(); // I like to use Read() over other options, because other options like the 'ExecuteScalar'-method require a cast.
        }
    }
}

Thx

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant