CODEWITHSUNDEEP
c#sqlselectescaping
ChrisP
ChrisP

Reputation: 47

Including an email address in a SQL SELECT statement that includes a single quote

I am working on a process that needs to retrieve records from a cosmos database that may include quotes in the email address. When building the select statement I have tried to replace the single quote with two single quotes, as I've found as a solution, but I'm still getting a syntax error. Perhaps it's because, in my first test, the single quote is the first character of the email address.

Here's an example of an email address and my code to build the SQL statement.

'[email protected]

                    var q = "SELECT * FROM c where c.enrollmentBrandRegion = '" + profile.enrollmentBrandRegion + "' and c.emailAddress = '" + profile.emailAddress.Replace("'", "''") + "'";
                    var query = new QueryDefinition(q);
                    var iterator = containerMaster.GetItemQueryIterator<Consumer>(query);

                    while (iterator.HasMoreResults)
                    {
                        var response = await iterator.ReadNextAsync();

                        foreach (var document in response)
                        {
                            masterOut.Add(document);
                            foundCount++;
                        }
                    }

When I set a break point and examine my query it looks like this:

"SELECT * FROM c where c.enrollmentBrandRegion = 'Brand_Region' and c.emailAddress = '''[email protected]'"

This is the error I'm receiving:

Error reading file: Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: ; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: ; Reason: ({"errors":[{"severity":"Error","location":{"start":85,"end":104},"code":"SC1001","message":"Syntax error, incorrect syntax near ''[email protected]''."}]}););

Is it possible to build the SELECT statement in such as way that it will retrieve the record? Any recommendations would be appreciated.

Upvotes: 0

Views: 42

Answers (0)

Related Questions