Passing parameters with apostrophes and wildcards from C++ to SQL

Question

Afternoon all

I've been working on a dll extension for an application, it's a report on customer activity and the first section is a user search to lookup the customer.

The query itself works fine but when I tried to make it SQL injection proof by parameter passing, I was unable to get it to work.

I tried having the apostrophes and wildcards in the main query but when I did the q->Params->ParamByName("ID")->AsString = param function threw an exception saying it could not find the parameter which I've confirmed is the effect of the apostrophes. When I pass in the apostrophes and wildcards as part of the parameter like below, there's no error but the query returns nothing.

I have tried escaping both the apostrophe and the wildcards but it made no difference.

Please let me know if I need to provide any more info.

TStringList* SQLString = new TStringList;
SQLString->Add("select p.id, p.givenName, p.surname, p.dateOfBirth, vm.MaxRowDate ");
SQLString->Add("from (  ");
SQLString->Add("    select pid, customer_systemID, customer_dbID, max(datetime) as MaxRowDate ");
SQLString->Add("    from Visit ");
SQLString->Add("    group by pid, customer_systemID, customer_dbID");
SQLString->Add("     ) vm ");
SQLString->Add("inner join Customer p ");
SQLString->Add("ON vm.pid = p.pid ");
SQLString->Add("and vm.customer_systemID = p.systemID ");
SQLString->Add("and vm.customer_dbID = p.databaseID ");

AnsiString joiningTerm("where ");

 if(id != "")
{
    SQLString->Add(joiningTerm+"id like :ID ");
    joiningTerm = "and ";
}

SQLString->Add("order by surname ");

TMSQuery* q;

try
{

    try
    {
        q = new TMSQuery(NULL);
        q->Connection = _db;


        q->SQL->Assign(SQLString);

        if(id != "")
        {
            AnsiString param("'%"+id+"%'");
            q->Params->ParamByName("ID")->AsString = param;
        }

        q->Open();

Here is my raw SQL query:

SELECT p.id, p.givenName, p.surname, p.dateOfBirth, vm.MaxRowDate
FROM
( 
    SELECT pid, customer_systemID, customer_dbID, MAX(DATETIME) AS MaxRowDate
    FROM Visit
    GROUP BY pid, customer_systemID, customer_dbID
) vm
INNER JOIN Customer p
ON vm.pid = p.pid AND vm.customer_systemID = p.systemID AND vm.customer_dbID = p.databaseID

Edit: It has been suggested to me that perhaps this is a limitation of the library. I am going to post on the Devart forums and will report back if I get a response.

I know this wasn't your question here, but since it's related, are you sure you need to create the query entirely dynamically? Perhaps this isn't the full use case, but it seems like this logic is simple enough could be encapsulated in a stored procedure, and then you wouldn't even have to worry about injection. — Xedni, Aug 20 '15 at 15:30
@Xedni The dynamic query is for the sake of portability, this dll would be installed at a number of sites each with their own database(some of which I don't have easy access to) and I'd rather be able to just deploy the dll without modifying the database. — Crackerjack55, Aug 21 '15 at 08:39
I think SQL Server needs `@` for the parameter marker. How are you making your connections to the database server? — shawnt00, Oct 14 '15 at 16:56
@shawnt00 I checked the query in the activity monitor in SQL management studio and SQL had showed @1, @2, @3 for the parameters so they were added automatically. — Crackerjack55, Oct 14 '15 at 21:04
Have you confirmed that the parameters are being passed correctly? I know you stated it returned no results but I'd still want to see it. — shawnt00, Oct 14 '15 at 22:34
@shawt00 Apologies for the overdue response, I tested the parameter passing by using an insert statement with the same value and it appeared in the database as expected with the wildcards present but the apostrophes stripped out. — Crackerjack55, Feb 25 '16 at 13:21

score 1 · Answer 1 · answered Oct 14 '15 at 16:53

1

One option here is to validate the user input. Walk through the input and remove any invalid characters or return an error and drop the entire input/transaction.

answered Oct 14 '15 at 16:53

Matt Jones MSFT

59
7

Thanks for your answer but the problem is not the user passing in invalid characters, the problem is that the query needs to search using wild cards and the value needs to be enclosed in apostrophes. – Crackerjack55 Oct 14 '15 at 21:08

Passing parameters with apostrophes and wildcards from C++ to SQL

1 Answers1