C#•13mo 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})";

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})";

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)

Angius•12/3/24, 11:52 PM

Hello SQL injection

Angius•12/3/24, 11:55 PM

OleDb .Net Framework

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

HazzzaOP•12/4/24, 2:30 PM

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

AAngius > OleDb .Net Framework Out of your own will, or are you being held at gunpoint? ...

HazzzaOP•12/4/24, 2:31 PM

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

Tenor

HHazzza The recipe string and ingredients string are adding in the number of parameters ...

TeBeCo•12/4/24, 5:28 PM

does it comes from the UI ?

TeBeCo•12/4/24, 5:29 PM

so if I just CURL your app and decide to send 0 or '1' = '1'; DROP DATABASE GO; --0 or '1' = '1'; DROP DATABASE GO; -- what do you think will happen ?

HazzzaOP•12/5/24, 4:40 PM

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

TeBeCo•12/5/24, 5:23 PM

this doesnt show sanitation code or if it's from UI

TeBeCo•12/5/24, 5:23 PM

so still injectable so far

TeBeCo•12/5/24, 5:23 PM

can't help you without more code regaring the original problem

TeBeCo•12/5/24, 5:24 PM

i'm existing this thread for others to continue here we seem to be on different Timezone and the async deal is hard

HazzzaOP•12/5/24, 5:47 PM

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
}

HazzzaOP•12/5/24, 5:48 PM

They replace all the question marks which the recipe string adds

SQL gives Missing operator error

Similar Threads

Similar Threads

Similar Threads