C#•3w ago

SQL gives Missing operator error

SQL:

C#
"SELECT * " +
"FROM IngredientRecipe " +
"INNER JOIN Product ON IngredientRecipe.ProductName = Product.ProductName " +
"INNER JOIN Recipe ON IngredientRecipe.ProductName = Recipe.ProductName " +
$"WHERE IngredientRecipe.ProductName IN ({RecipeString}) AND IngredientName IN ({IngredientsString})";

C#
"SELECT * " +
"FROM IngredientRecipe " +
"INNER JOIN Product ON IngredientRecipe.ProductName = Product.ProductName " +
"INNER JOIN Recipe ON IngredientRecipe.ProductName = Recipe.ProductName " +
$"WHERE IngredientRecipe.ProductName IN ({RecipeString}) AND IngredientName IN ({IngredientsString})";

My old SQL does work with only 1 other table being join on Old SQL:

C#
"SELECT * " +
"FROM IngredientRecipe " +
"INNER JOIN Product ON IngredientRecipe.ProductName = Product.ProductName " +
$"WHERE IngredientRecipe.ProductName IN ({RecipeString}) AND IngredientName IN ({IngredientsString})";

C#
"SELECT * " +
"FROM IngredientRecipe " +
"INNER JOIN Product ON IngredientRecipe.ProductName = Product.ProductName " +
$"WHERE IngredientRecipe.ProductName IN ({RecipeString}) AND IngredientName IN ({IngredientsString})";

I also tried with just the Recipe table being joined on and that also worked so seems to only happen when both tables are being joined. (Im using OleDb .Net Framework)

7 Replies

Angius•3w ago

Hello SQL injection

OleDb .Net Framework

Out of your own will, or are you being held at gunpoint? Blink twice if you need help

HazzzaOP•3w ago

The recipe string and ingredients string are adding in the number of parameters required so its not prone to SQL injection

HazzzaOP•3w ago

https://tenor.com/view/blink-twice-pointing-beard-eyes-gif-16953299

Tenor

Unknown User•3w ago

Message Not Public

HazzzaOP•3w ago

C#
string RecipeString = "";            
int count = 0;
foreach (string name in Recipes.elements)
{
   if(name != "")
   {
      RecipeString += "?,";
      Parameters[count] = name;
      count++;
   }
}

C#
string RecipeString = "";            
int count = 0;
foreach (string name in Recipes.elements)
{
   if(name != "")
   {
      RecipeString += "?,";
      Parameters[count] = name;
      count++;
   }
}

This is the code to generate RecipeString

Unknown User•3w ago

Message Not Public

HazzzaOP•3w ago

The parameters are sent off the the SQL string

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

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

Then are inserted as parameters into the command

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
}

They replace all the question marks which the recipe string adds

Gaming

Programming

SQL gives Missing operator error