For SOQL queries, can “WITH USER_MODE” safely replace “WITH SECURITY_ENFORCED”

You should check that the current user has isAccessible() permission on the fields in the WHERE clause as well as any fields in the SELECT clause being returned to the logged in user. The reason why you need to do this check is that Apex will not throw a DML error when accessing fields that the user cannot access, so it is up to you to enforce the platform's permission model.

There is some confusion if your intuition comes from working in the developer console or via the API. The developer console runs in an execute anonymous context in which permissions are enforced. Permissions are enforced in the API as well. Permissions are not enforced in apex code running in your org, so you as the developer need to enforce them. You will not get permissions exceptions.

Also, be aware of the difference between profile perms, CRUD/FLS perms, and record sharing. Profile perms are always enforced but are only refreshed across logins. CRUD/FLS are never enforced which is why you need to make all those .isAccessible(), and isCreatable() calls before accessing, updating, deleting or inserting records. Record sharing is enforced if your class is 'with sharing'.

In terms of implementation, it's up to you as the dev to enforce programmatically whatever isn't enforced automatically by the platform, where appropriate. In some cases you may want to set a field as private/private for sharing and then ignore sharing when accessing the field. This should only be done for custom fields. In the case of sites and guest user access, you may also need to ignore CRUD/FLS, but again this should only be done for custom fields that are in your namespace. For standard fields you must always enforce CRUD/FLS (no exceptions), and you must always enforce sharing in the entry-points to your app (controllers and global classes). Hide the without sharing code in library functions invoked downstream from your entry points.

Currently if you include a field in a SQOL query that a user doesn't have access, the field is returned and can be used by the code, thus exposing the data to someone that should not have access to it.

This new clause is a blunt way to ensure your code isn't provided access to fields that an admin may have specifically restricted access to via the profile. It does this by throwing an exception if there are any fields in the query that the context user doesn't have access to.

This feature is tailored to Apex developers who have minimal development experience with security and to applications where graceful degradation on permissions errors isn’t required.

You may want to use the isAccessible or similar fields to provide a better experience to your users, but that requires more logic and error handling. I highly doubt they'd be deprecated as they give the developer much more control when handling FLS.

If a field is hidden, the exception will bubble up to whatever the calling code is.
e.g. If it's a controller on a page, and you have the pageMessages on your VF page, then the exception will be shown there.