0

Can anyone think of anything wrong with this check for a valid SQL Server Like expression? I guess maybe the last group is redundant as it is covered by the third group. Anyway - suggestions or improvements welcome.

^(\S*%\S*|\S*_\S*|\S*\[\S+\]\S*|\S*\[^\S+\]\S*)$

I'm referencing this as my guide: Microsoft Docs for SQL Server LIKE

Here are the tests I've written:

    [TestMethod]
    public void SqlLikeIsValidSqlLikeTest()
    {
        Assert.IsTrue("123%456".IsValidSqlLike());
        Assert.IsTrue("%123456".IsValidSqlLike());
        Assert.IsTrue("123456%".IsValidSqlLike());
        Assert.IsTrue("123_456".IsValidSqlLike());
        Assert.IsTrue("_123456".IsValidSqlLike());
        Assert.IsTrue("123456_".IsValidSqlLike());
        Assert.IsTrue("1_23%456".IsValidSqlLike());
        Assert.IsTrue("123%45_6".IsValidSqlLike());
        Assert.IsTrue("123%456".IsValidSqlLike());
        Assert.IsTrue("12[34]56".IsValidSqlLike());
        Assert.IsTrue("123[1-2]456".IsValidSqlLike());
        Assert.IsTrue("123[1234]456".IsValidSqlLike());
        Assert.IsTrue("123[^1234]456".IsValidSqlLike());
        Assert.IsTrue("[1234]456".IsValidSqlLike());
        Assert.IsTrue("123[1234]".IsValidSqlLike());
        Assert.IsTrue("123[^1-4]456".IsValidSqlLike());
        Assert.IsTrue("[^1-4]456".IsValidSqlLike());
        Assert.IsTrue("123[^1-4]".IsValidSqlLike());
        Assert.IsTrue("12[3-]56".IsValidSqlLike());
        Assert.IsTrue("12[^]56".IsValidSqlLike());
    }

    [TestMethod]
    public void SqlLikeIsNotValidSqlLikeTest()
    {
        Assert.IsFalse("123456".IsValidSqlLike());
        Assert.IsFalse("12[3456".IsValidSqlLike());
        Assert.IsFalse("1234]56".IsValidSqlLike());
        Assert.IsFalse("12]34[56".IsValidSqlLike());
        Assert.IsFalse("]34[56".IsValidSqlLike());
        Assert.IsFalse("34[^56".IsValidSqlLike());
        Assert.IsFalse("12]34[".IsValidSqlLike());
        Assert.IsFalse("12[]56".IsValidSqlLike());
    }
John Mann
  • 31
  • 5
  • 2
    SQL Server doesn't support Regex. It uses an expression that does contain some similarities to Regex, but it is quite far from it in functionality. I can't read Regex I'm afraid, however, if you explain what you're trying to achieve here, I'll be happy to try and provide a SQL `LIKE` expression that'll work for your needs. – Thom A Jun 29 '18 at 11:14
  • @Larnu - the question is not about using a regex expression in SQL, but about using regex to check that an SQL Like clause is valid. – PaulF Jun 29 '18 at 11:18
  • 2
    Rather than showing us that hideous regex, can you show us data which it is supposed to be matching? – Tim Biegeleisen Jun 29 '18 at 11:18
  • 1
    What do you mean check if a SQL `LIKE` clause is valid? Where are you, therefore, doing your Regex check? Can you elaborate on your scenario please. – Thom A Jun 29 '18 at 11:18
  • 2
    What makes a like expression invalid? – ProgrammingLlama Jun 29 '18 at 11:19
  • @PaulF May i suggest you update the question be more clear, and also to include examples of text that you want to "approve" + examples of text that you want to "disapprove" using your regex. – Peter B Jun 29 '18 at 11:20
  • @PeterB - it is not my question to update, I was clarifying what OP was asking. All I would update is removing the C# tag. – PaulF Jun 29 '18 at 11:21
  • 1
    As far as anything wrong with the regex - it doesn't appear to allow expressions with no wildcard characters & it doesn't appear to allow for multiple wildcard characters (other than the \S will eat up any after the first) & it doesn't allow for any whitespace characters. Also the expression needs surrounding with single/double quotes. – PaulF Jun 29 '18 at 11:35
  • Wouldn't the use of a Sql parser be enought to check if the like part is correct ? Because Ms has using Microsoft.Data.Schema.ScriptDom.Sql; T sql parser if needed – Drag and Drop Jun 29 '18 at 11:47
  • 2
    `LIKE ` is valid for any `` that is a valid T-SQL string expression. You will never get an error for any string fed to a `LIKE` clause, it will just not match what you're expecting if you "get it wrong" (but you don't know if matching `a-z]%` isn't exactly what the user wanted, instead of being a typo for `[a-z]%`). Your check for validity is either unnecessary, or should be constrained by business concerns, not based on pure syntax. – Jeroen Mostert Jun 29 '18 at 11:55
  • This isn't part of the question, but some people have said they can't read regex. If you go to https://regex101.com/ and paste in a regex expression it will tell you what it is doing. You might find that helpful in future if you need to figure out a regex expression. – John Mann Jun 29 '18 at 14:41
  • Rejecting `123456` is particularly problematic. There should be no prohibition on using `LIKE` to match exact values, because that's the sort of thing that system could generate automatically, and SQL Server certainly has no problem with it. Conversely, a pattern like `[]]` is accepted by your check even though that's not a valid pattern for matching purposes (it doesn't match anything, not even `]`). It's really unclear what you're trying to *achieve* with this check -- why not leave the filtering to SQL Server? – Jeroen Mostert Jun 29 '18 at 15:12
  • I need to check that it is a patterned like - not the same as a plain equals - so any valid SQL Server pattern that can be used by a LIKE. But I hadn't realised []] was a special case - thank you for pointing that out. – John Mann Jul 02 '18 at 10:32
  • Just to note that my question was asking if anyone had a better RegEx to do this and so far no one has. I think it is clear what I want - a check that there is a valid SQL Server pattern used by LIKE. If there is no pattern match the RegEx should return a false. So although it is true that LIKE 'abc' won't error, it is the same as = 'abc' and hence not a pattern match. – John Mann Jul 02 '18 at 10:37

1 Answers1

0

This may not the exact solution you may be looking for, but a way around to solve your problem.

If you have an SQL connection, you can avoid the need to write an SQL parser, by using the "SET NOEXEC ON" command before executing your SQL command. This tells SQL not to execute any commands, just evaluate if they would have been executed.

SqlCommand cmd = new SqlCommand("SET NOEXEC ON", conn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(sqlToBeChecked, conn);
cmd.ExecuteNonQuery();

If no exception is thrown, all is fine.

Or here are the other ways to do Query Validation using c#

Hope it will help you

Diptee Hamdapurkar
  • 372
  • 2
  • 9
  • I did see the SQL parser answer but it seemed too much for what I wanted. Also I'm working with services and would need to make a call to the service to run the SQL on the service then get the result, whereas I'm sure I can do what I want with a regex expression. It is just I'm no expert at regex and while I believe this works (I've written some tests which run ok) I'd appreciate some confirmation. – John Mann Jun 29 '18 at 14:37