How to use Is null or empty in raw sql for assigned value which is not in table?

Question

How to check IsNullOrEmpty in RawSQL .If control number and sender Id both have values,then check like condition for both.If it having sender id alone check like condition for sender id alone and If it having controlnumber alone check like condition for control number alone. It shows error like

Incorrect syntax near *

using (var context = new BSoftWEDIIContext())
                {

                    if (!string.IsNullOrEmpty(controlNumber))
                    {
                        controlNumber = "*" + controlNumber + "*";
                    }
                    if (!string.IsNullOrEmpty(senderNumber))
                    {
                        senderNumber = "*" + senderNumber + "*";
                    }

                    var fileDetail = context.FileDetails
                        .SqlQuery("select * from FileDetails where @" + controlNumber.ToString()
                        +" is not null OR CONVERT(varchar(max), RawData) like '%" + controlNumber.ToString() 
                        + "%' AND CONVERT(varchar(max), RawData) like '%" + senderNumber.ToString()+"%'").ToList();
                    matchedFileId = fileDetail?.Select(a => a.Id).ToList();

                }

**Pro Tip** : Please learn now to format your code and question in readable way — TheGeneral, Jan 17 '19 at 08:35
You are wide open for sql injection. You should always use parameterized queries instead of string concatenation. You can also omit the `ToString` after the `strings`. — Tim Schmelter, Jan 17 '19 at 08:39

score 1 · Accepted Answer · answered Jan 17 '19 at 08:47

You are appending too many * and the resulting query is not building properly. Check for the condition accordingly:

using (var context = new BSoftWEDIIContext())
            {

                if (!string.IsNullOrEmpty(controlNumber))
                {
                    controlNumber = controlNumber;
                }
                if (!string.IsNullOrEmpty(senderNumber))
                {
                    senderNumber = senderNumber;
                }

                var fileDetail = context.FileDetails.SqlQuery("select * from FileDetails where " + controlNumber.ToString() + " is not null" + " OR CONVERT(varchar(max), RawData) like '%" + controlNumber.ToString() + "%' AND CONVERT(varchar(max), RawData) like '%" + senderNumber.ToString() + "%'").ToList();
                matchedFileId = fileDetail?.Select(a => a.Id).ToList();
            }

Note: This approach is prone to SQL Injection and is not recommended at all. Please read about parameterised query and best practices from below links:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/how-to-execute-a-parameterized-entity-sql-query-using-entitycommand

how to change sql statement to parameterized query?

How to use Is null or empty in raw sql for assigned value which is not in table?

1 Answers1