Comparing a string to it's equal in a database returns false

Question

I would like to find in my database which line has its "path" field's value equal to the string "c:\something\somethingelse\anotherthing.thing". I found the line by browsing and copied it's content, before making an SQL request:

On C# side, my code looks like this:
EDIT: Due to @CompuChip 's comment, I edited my line for something that I hope may be better

    String MyPath = "c:\\something\\somethingelse\\anotherthing.thing"
    MyPath = String.Format("select * from x where path = '{0}'", MyPath);

Then I called the method to create and send the request to my database but I got an exception

I tried the request itself on my database but even if I copied the exact value it couldn't find the line: Here's what I tried:

Select * from x where path = "c:\something\somethingelse\anotherthing.thing"
Select * from x where STRCMP(path, "c:\something\somethingelse\anotherthing.thing") = 0

Is there a good way to compare the strings correctly and find my line ? Thank you for your time and have a nice day.
Edit 2:
I also tried getting all my lines in C# then comparing them one by one.
It worked, but with 10K+ lines it's beginning to consume resources

"*but I got an exception*" - and what exception would that be? — germi, Nov 28 '19 at 09:52
"you have an error in your sql synthax" near Select "c:\something\somethingelse\anotherthing.thing" Exception only occurs on C# side — Ryan L, Nov 28 '19 at 09:54
Please please _please_ don't construct your SQL queries like that. https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements Not only is adding parameters the safe thing to do, it will also make your quoting problem a non-issue. — CompuChip, Nov 28 '19 at 09:57
Most probably, the database wants you to put the path in single quotes `select 'c:\...'`. Anyway, if your problem is entirely on database side you should add the tag for your database vendor (sqlserver, oracle, mysql, etc.) and show us the complete query. On the other hand if your problem is with C# then you should post at least an actual line that generates an exception, or, even better, [a reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). — default locale, Nov 28 '19 at 09:58
when you use = in SQL it will check for equal match including the case and white spaces. Also string are quoted in single quote like 'keyword' not "keyword" — Miller, Nov 28 '19 at 10:01
The value is the exact same, I checked by comparing with equals on C# side — Ryan L, Nov 28 '19 at 10:05
Can you find the record using a part of the string, e.g. `select * from x where path like '%anotherthing%'`? — default locale, Nov 28 '19 at 10:18
I tried using "like '.thing' " and couldn't find anything Edit: I used "Like '%\anotherthing.thing' " and it couldn't find anything, but when I used like without any backslash I managed to find the line — Ryan L, Nov 28 '19 at 10:21
It appears I have to do the same thing I do in C# and put two backslashes instead of one in the path, even in MySQL The request works on MySQL but I still get the error in C# — Ryan L, Nov 28 '19 at 10:30

CompuChip · Accepted Answer · 2019-11-28T10:35:59.960

3

Please, please, please don't construct your SQL queries like that.

The proper solution is using parameters, as explained here: Why do we always prefer using parameters in SQL statements?

Your C# code would become something like this (adapted from the linked answer):

string sql = "select * from x where path = @path";

using (SqlCommand command = new SqlCommand(sql, connection))
{
    var pathParam = new SqlParameter("path", SqlDbType.VarChar);
    pathParam.Value = @"c:\something\somethingelse\anotherthing.thing";

    command.Parameters.Add(pathParam);
    var results = command.ExecuteReader();
}

or, for MySql,

string sql = "select * from x where path = ?path";

using (MySqlCommand command = new MySqlCommand(sql, connection))
{
    var pathParam = new MySqlParameter("path",
      @"c:\something\somethingelse\anotherthing.thing");

    command.Parameters.Add(pathParam);
    var results = command.ExecuteReader();
}

Assuming that you (have checked that you) actually have a matching record in the database, the problem is likely either with the quoting, or the escaping of the value. If that is indeed the case, using parameters to retrieve the record is not only the safe thing to do, it will also make your quoting or escaping problem a non-issue.

edited Nov 28 '19 at 10:35

answered Nov 28 '19 at 10:05

CompuChip

9,143
4
24
48

Oh my bad I just edited my post and then saw your answer, I'm gonna change my code – Ryan L Nov 28 '19 at 10:08
I think the question is now targeting MySQL. Update your answer to use `MySqlCommand` instead, although the crux remains the same. – Tanveer Badar Nov 28 '19 at 10:09
The problem seems to be because I'm looking for a file path (with Backslashes etc ), When I'm looking for another type of string, let's say "name" I will find some lines – Ryan L Nov 28 '19 at 10:19
Thanks @TanveerBadar, added, but I don't use MySQL a lot so I hope I got the syntax right. – CompuChip Nov 28 '19 at 10:34
Yup, looks fine to me. – Tanveer Badar Nov 28 '19 at 10:35
@RyanL if you still see the problem with the code from my answer, the error may be in the data. If you do a SELECT without the WHERE and look at the output, does it look right? Or is data stored with forward slashes, perhaps? Is the type of your column CHAR or VARCHAR? – CompuChip Nov 28 '19 at 10:37

score 1 · Answer 2 · answered Nov 28 '19 at 10:43

1

It appears I have to do the same thing I do in C# and put two backslashes instead of one in the path, even in MySQL

Furthermore I also had to construct correctly my requests as @CompuChip hinted.

Thank you all for your help !

answered Nov 28 '19 at 10:43

Ryan L

31
8

Comparing a string to it's equal in a database returns false

2 Answers2