Help with Model in DB - Sqlite and dapper

Hi Robb,

need to ask a question.
Your problem is
  - how to get the parameter from the WinForm ComboBox?
or
  - how the get the parameters into your LoadFormId() function?

leflon

The select statement needs a where statement that is a variable.    That's first part.  It will have two where clauses.

Just was pointing out the values of the variables will come from value in combo box and a,search box from UI of winform

So is this vulnerable.  My understanding is tgis is how dapper works.


Both of these values will be strings.

.if one was an int how would you do it?

Robb,

If I am correct, building the final SQL query by concatenating strings, without validating the variable parts which come from the outside, is vulnerable to attacks.

When I kind of know how the final query is structured I can utilize it.
With the query like "select * from foo where form=X", what happens if I pass in a string for X like "bar; drop table foo;"?
If the resulting query is passed and executed, the foo table is gone.

So the arguments should either be none strings. Or the strings have to be validated.

If you pass in an int type, nothing really changes. Just the signature of the LoadFormId function

public static List<Attachments> LoadFormId(string form, string requestID)

Select allOpen in new window

The rest stays unchanged.

leflon

I saw some more complex examples where dapper and sql lite handke injection but it wasn't clear to me.  I needed a more simole explanation to get the syntax understood.   They have some sql in examples...but not best examples

Here is an example from their website.  This is supposed to prevent sql injection...
How would you refactor?  Here is the link to their website.
https://dapper-tutorial.net/parameter-string

var sql = "SELECT * FROM Invoice WHERE Code = @Code;";

using (var connection = My.ConnectionFactory())
{
	connection.Open();

	var invoices = connection.Query<Invoice>(sql, new {Code = new DbString {Value = "Invoice_1", IsFixedLength = false, Length = 9, IsAnsi = true}}).ToList();

	My.Result.Show(invoices);
}

Select allOpen in new window

I have to admit dapper is pretty new to me.
But looking at the example the question that comes to my mind is:

• How is the parameter "Value = "Invoice_1" be filled?
• And how ist the parameter "Length = 9" be filled?

You gotta know that Value is 9 chars long?
And if its "Invoice_10"?

So with the example my problem is on how you get and validate the Value und Length parameters.

Maybe the "Dynamic" part is the way to go. So the second paramter @Code parameter is again a string,a nd as such a potential security hole

// Dynamic
DynamicParameters parameter = new DynamicParameters();

parameter.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);

Select allOpen in new window

dapper states:

Is Dapper SQL Injections safe?

Yes, it's 100% safe if you use parametrized queries as you should always do!

What exactly are "parametrized queries"?

If you have a free text box as an input, you need to verify, that it doesn't hold any potential harmful code.
If the framework supports you in some way, I can't tell you right know. But someone needs to validate the input.

You can setup the combobox from your original post, to only hold valid entries.
But what about the "search box"?
If that's a free text field, someone has to validate the input.
And I believe it's you.
Either directly in the code behind, or indirectly by using some functionality of the dapper framework.

I am sorry, but the example is too simplistic for me to really refactor. The key here is how do you get the Value and the Length parameter. And sadly, that's not part of the example.

leflon

lefon:

Here is how I have it implemented....but am getting no results on my test when I know per the SQL atleast that it should return something.

 public static List<Attachments> LoadAttachments(Attachments attachments)
        {
            using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
            {
                var output = cnn.Query<Attachments>(
                    "select * from Attachments WHERE RequestID = @RequestID AND Form = @Form",
                     new
                     {
                         @RequestID = attachments.RequestID.ToString(),
                         @Form = attachments.Form.ToString()
                     });
                return output.ToList();
            }
        }

Select allOpen in new window

Here is where i call from the form.  I can validate at this point that the strings for requestid and form are assigned. yet attachment returns 0 when in my test it should return 2.  Something is wrong in the "dapper" query.

 Attachments a = new Attachments();

                a.RequestID = txtRequestID.Text;
                a.Form = cmbFormType.Text;

                attachment = SqliteDataAccess.LoadAttachments(a);

Select allOpen in new window

Ok scratch that.  Not sure how that works...going back to the way it was discussed earlier...Its not loading as string.

I call like so:

                attachment = SqliteDataAccess.LoadAttachments(cmbFormType.Text, txtRequestID.Text);

Select allOpen in new window

And here is the LoadAttachments
Both of these values should be surrounded in Quotes as they are strings.

 public static List<Attachments> LoadAttachments(string form, string requestID)
        {
            using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
            {
                var query = string.Format("select Id,RequestID,Form Document from Attachments where Form = {0} and RequestId = {1}", form, requestID);
                var output = cnn.Query<Attachments>(query, new DynamicParameters());
                return output.ToList();
            }
        }

Select allOpen in new window

Ok ..refactored and I stepped through code.

Var query in this statement...I can copy and paste the results, run in SQl...and it returns records...
but var output has 0 records.

  public static List<Attachments> LoadAttachments(string form, string requestID)
        {
            using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
            {
                var query = string.Format("select Id, RequestID, Form, Document from Attachments where Form = '{0}' and RequestId ='{1}'", form, requestID);
                var output = cnn.Query<Attachments>(query, new DynamicParameters());
                return output.ToList();
            }
        }

Select allOpen in new window

arrrrg....I had padded data when converted this table from SQl Server to SQL lite...so I had to use the SQLite trim function as well.  

     var query = string.Format("select Id, RequestID, Form, Document from Attachments where Form = '{0}' and trim(RequestId) ='{1}'", form, requestID);



Thanks so much for your help.