0

I have validation logic at my model level service level and UI level.

Example:

    public int Id { get; set; }

    [Required]
    public string FirstName { get; set; }

    [Required]
    public string LastName { get; set; }

    [Required]
    [RegularExpression(RegularExpressions.UK_PHONE_REGEX)]
    public string PhoneNumber { get; set; }

    public string Notes { get; set; }

I'm encountering an issue where I'm able to manually input data into my database using SSMS, even if it doesn't pass the validation rules I've set in my code.

For example, I can enter a phone number like '8' or '10000', which should not be allowed according to the regex pattern I'm using.

However, when I tested the regex on an external site like https://regex101.com/, it correctly did not accept those values. Is there a way to enforce the validation rules in the database itself to prevent this issue?

I also used Entity Framework Core for the database connections, so Add-Migration and Update-Database was done.

Alexei Levenkov
  • 98,904
  • 14
  • 127
  • 179
jacky
  • 65
  • 1
  • 8
  • 2
    SQL Server doesn't support Regex natively. You can either use a SQLCLR function, or use some kind of `LIKE` eg `LIKE 0[1-3][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]` – Charlieface May 16 '23 at 00:50
  • 3
    You can add CONSTRAINTs to your database, but these will need to re-implement your validation logic. – SSS May 16 '23 at 00:54
  • You can intercept the save method in ef core, then find all modified entities, then validate them. – Jeremy Lakeman May 16 '23 at 01:02
  • i don't think sql server has regex validation for column constraint in straight forward way, see [QA](https://stackoverflow.com/q/11223245). you could use custom sql functions as the QA says though - chug it somewhere in migration. i believe keeping two regexes (in app side and database side) duplicates functionality and if its not well documented it will be pain for future developers. – Bagus Tesa May 16 '23 at 04:22
  • The code in your application isn't run when you enter stuff in the database directly, so the validation isn't done at all. That is possible to achieve by using stuff like CLR etc, but it's not easy. As other said, a check constraint might be able to do it, but the support for regex is weak in sql server (unless using CLR functions) – siggemannen May 16 '23 at 07:46

0 Answers0