To "escape" single quotes in SQL Server, you have to double them up meaning you put two single quotes back-to-back:

SELECT * FROM [Login] where [User] = 'Frances'' test of SQL''s escaped quotes';

Notice that the escaped quotes in the example are two separate single quotes, not one "double" quotation mark.

Thanks