C#•3w ago

SQL: Trying to obtain all rows that have a primary key inside a inputted table

The code works if the Recipe string replaces the ? in the string. However, as I want to prevent SQL injection the RecipeString isn't treated as SQL so the ' ' either side of each name in RecipeString isn't identified in the SQL string hence returning no values. Hopefully you understand the issue. If anyone has any ideas on how to fix this it would be greatly appriciated. Thanks Code:

C#
string _sSqlString = "SELECT * FROM IngredientRecipe WHERE ProductName IN (?)";

string RecipeString = "";

foreach(string name in Recipes.elements)
{
    if(name != "")
    {
        RecipeString += $"'{name}',";
    }
}
RecipeString = RecipeString.Remove(RecipeString.Length - 1);


string[] Parameters = new string[1];
Parameters[0] = RecipeString;

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, Parameters);

C#
string _sSqlString = "SELECT * FROM IngredientRecipe WHERE ProductName IN (?)";

string RecipeString = "";

foreach(string name in Recipes.elements)
{
    if(name != "")
    {
        RecipeString += $"'{name}',";
    }
}
RecipeString = RecipeString.Remove(RecipeString.Length - 1);


string[] Parameters = new string[1];
Parameters[0] = RecipeString;

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, Parameters);

Example Recipe String is:

'bread','pizza'

'bread','pizza'

4 Replies

Keswiik•3w ago

You should be using a parameterized query, ex: https://stackoverflow.com/questions/10898737/parameterize-sql-query

Stack Overflow

Parameterize SQL query

Many posts about Parameters in SQL with C# but I am still missing something. I am not getting an error message but no data is inserted. What is missing? I have text boxes named fname, lname, address,

HazzzaOP•3w ago

Sorry I didn't explain it well, I am currently using parameters in all my sql requests (code for the ExectuteSqlQuery method below), the parameters code is all working however as I am including the ' ' either side of the recipe names inside the parameter, the SQL code doesn't recognise the ' ' which are needed for the SQL to recognise each individual recipe name, so the IN method doesn't work. (There are no error messages aswell)

C#
public DataTable ExecuteSqlQuery(String sSqlString, string[] Parameters)
{
    OleDbConnection cnn = new OleDbConnection(CONNECTION_STRING); // Create a new connection to the database
    OleDbCommand cmd = new OleDbCommand(sSqlString, cnn); // sSqlString string contains a SQL statement to run on the database

    if (Parameters != null)
    {
        for (int i = 0; i < Parameters.Length; i++)
        {
            cmd.Parameters.Add(new OleDbParameter { Value = Parameters[i] });
        }
    }

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataTable dt = new DataTable();

    try
    {
        cnn.Open();
        da.Fill(dt);
        cnn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Product Management System", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
    return dt; // Return the data retrieved by the query
}

C#
public DataTable ExecuteSqlQuery(String sSqlString, string[] Parameters)
{
    OleDbConnection cnn = new OleDbConnection(CONNECTION_STRING); // Create a new connection to the database
    OleDbCommand cmd = new OleDbCommand(sSqlString, cnn); // sSqlString string contains a SQL statement to run on the database

    if (Parameters != null)
    {
        for (int i = 0; i < Parameters.Length; i++)
        {
            cmd.Parameters.Add(new OleDbParameter { Value = Parameters[i] });
        }
    }

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataTable dt = new DataTable();

    try
    {
        cnn.Open();
        da.Fill(dt);
        cnn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Product Management System", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }
    return dt; // Return the data retrieved by the query
}

This works

C#
string[] Parameters = new string[1];
Parameters[0] = "bread";

string _sSqlString = $"SELECT * FROM IngredientRecipe WHERE ProductName IN ('{Parameters[0]}')";

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, null);

C#
string[] Parameters = new string[1];
Parameters[0] = "bread";

string _sSqlString = $"SELECT * FROM IngredientRecipe WHERE ProductName IN ('{Parameters[0]}')";

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, null);

This doesn't

C#
string _sSqlString = $"SELECT * FROM IngredientRecipe WHERE ProductName IN ('?')";

string[] Parameters = new string[1];
Parameters[0] = "bread";

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, Parameters);

C#
string _sSqlString = $"SELECT * FROM IngredientRecipe WHERE ProductName IN ('?')";

string[] Parameters = new string[1];
Parameters[0] = "bread";

DataTable result = databaseUtils.ExecuteSqlQuery(_sSqlString, Parameters);

but the parameters do work in other SQL requests

Keswiik•3w ago

may need to make a new parameter for each value you're passing in, should be easy to format your query to handle this

HazzzaOP•3w ago

I tried making a new parameter for each value however that didn't seem to work due to the second scenario here tried it again seemed to fix it thanks

Gaming

Programming

SQL: Trying to obtain all rows that have a primary key inside a inputted table