Thursday, February 6, 2020

Executing raw SQL with EntityFramework [sic]

Throughout the interwebs, you will find a solution for executing raw SQL using EntityFramework:

dbContext.DbSet.FromSql(queryString)

This method does not actually execute raw SQL. It interprets the SQL query and then attempts to generate a LINQ query from it. This will fail if you have SQL methods in your query string. It can also fail for some parameter types. Finally, even if it works, it will wrap your query in another query in order to make it compatible with LINQ, whether or not you actually use LINQ.

In general, this is not a useful method in my opinion.

If you do not need to retrieve data, there's another method available that actually executes raw SQL:

dbContext.DbSet.ExecuteSqlCommandAsync(queryString);

This command returns an int reflecting the number of records affected.


But what if I want to perform a SELECT query using raw SQL? Well, EntityFramework has no option for you. However, you are in luck because there's another library that can be used with EF to accomplish your goal. It's called Dapper.

Dapper provides several raw SQL execution methods that can be applied to an IDbConnection. And you can access the IDbConnection from EF, like so:
using Dapper;

IDbConnection dbConnection = dbContext.Database.GetPgSqlConnection();
var results = await dbConnection.QueryFirstOrDefaultAsync(queryString);


The QueryFirstOrDefaultAsync method is an extension method provided by Dapper.

Tuesday, February 4, 2020

Hiding pre-production ASP.NET sites from robots (search engines, web crawlers)

As software developers, we love to put things out into the world for people to see and play with. Sometimes there's a manager behind us, or a business analyst in front of us asking us to hurry up.

This drive to deploy can lead to us skipping some important steps, such as preventing search engines from publishing our test and demo environments. To avoid this, we can employ two different tactics.

1. robots.txt

This is a file that has been around for ages with a singular purpose; to tell robots what to do when they encounter your website. Not all robots listen to our instructions, but we don't necessarily need to worry about those ones. The most problematic robots behave well but have a big impact on us. These robots are search engine web crawlers.

When a search engine crawls your website, it publishes your website to people searching for things related to your site. This may not impact you, but you could end up with a real customer accidentally visiting your beta or test site on accident. You may also end up with a lot more curious people visiting your test site than you'd like.

ASP.NET robots.txt.cshtml

If you're using a modern version of ASP.NET, you can create a simple Razor Page that will tell robots to go elsewhere for your non-production sites.

This file should be placed with your other Razor Pages in the Pages directory in your ASP.NET website project. You only need to have MVC enabled in Startup.cs to use this, and it doesn't require any controller logic because Razor Pages are magical.

What this Razor Page does, is generate a robots.txt file in your website's root folder. For non-production environments, the robots.txt file tells robots to avoid interacting with the entire site. For production, it tells them to avoid crawling the /hidden/path, which is just a placeholder for any routes on your site that you don't want to be indexed by a search engine.

2. Web Access Firewall (WAF)

You may be more worried about security bugs on your non-production websites, than them showing up in search results. You may also have an admin portal that you only want accessible from your coworkers and business partners.

The best way to protect sensitive areas on your website is definitely not a robots.txt. For one thing, it doesn't prevent a person or a robot from entering the restricted area, rather it just tells them that they souldn't. It's like a realy low-key keep out sign. The other problem with highlighting a restricted route with robots.txt is that it highlights the sensitive area for hackers to exploit.

A WAF can restrict access to sections of your website to specific blocks of IPs. It is quite common for companies to have a section of their website only available to people on their network. To accomplish this, they create a WAF routing rule that only allows their block of IP addresses from accessing certain routes.

I will not go into the details of doing this as it is dependent on where you're hosting your website, and the web host platform you are using. However, I think the terminology I have provided will help you find what you need on the internet.

Wednesday, January 22, 2020

Troubleshooting cellular connectivity issues on the Sony Xperia XA2 with T-Mobile

I have done a couple of things to my Xperia XA2 to improve my LTE connection, and I'm not sure what will help you the most.

Reset wireless settings
I did this sometime around September when I was unable to make calls or send texts, but was able to use mobile data:
  1. Open the Settings app
  2. Go to System > Advanced > Reset options > Reset Wi-Fi, mobile & Bluetooth
  3. Reset settings
Disable LTE Power Savings Feature
This feature was constantly causing my mobile connection to drop, resulting in my phone not being able to receive calls, texts. Sometimes I couldn't even make calls.
  1. Open the Settings app
  2. Enable Developer mode
    1. Go to About phone
    2. Scroll down to the bottom
    3. Tap on Build number 10 times
    4. Go back to main settings menu
  3. Go to System > Advanced > Developer options
  4. Scroll about halfway down the menu until you find the Networking section
  5. Enable the Mobile data always active option.

Monday, January 20, 2020

How to set up Microsoft Authenticator on LineagOS without Google Play Services

I use the Microsoft Authenticator app on Android because it has one of the best interfaces for a MFA app, and it makes it easy to migrate to a new device. Well, normally it does anyways.

I recently install LineageOS w/ microG onto my Sony Xperia XA2 and had a heck of a time restoring my accounts in the Microsoft Authenticator app. It turns out that the app requires the Google Messaging Service (GMS) to add an MS account, and you need an MS account to restore your account backups. Of course it doesn't tell you any of this. No, it just gives you a generic error or sits there spinning its wheels forever.

Anyways, here's how to enable Microsoft Authenticator using microG:
  1. Open the Settings app
  2. Go to System > Advanced > microG Settings
  3. Enable Google device registration
  4. Enable Google Cloud Messaging
  5. Add your Google account
      Don't worry, microG will still try its best to hide you from the all seeing eyes of Google.
  6. If you have Microsoft Authenticator installed already:
    1. View App Info (long tap on the app icon)
    2. Clear Storage
  7. Install Microsoft Authenticator if you don't already have it installed
  8. Open Microsoft Authenticator
  9. Tap skip a bunch of times until you see the "Restore" button at the bottom.
  10. Tap the Restore button and log into your primary Microsoft account
  11. Everything should restore now 

Note, that if you want to receive push notifications, such as Microsoft account login requests, you may need to check in to the microG push notification service called Firebase Cloud Messaging.

To test push notifications:
  1. Open your microsoft account
  2. While logging in, choose to use your Microsoft Authenticator app
  3. If you don't get a notification, then you need to Check In to Firebase
To check in to Firebase Cloud Messaging:
  1. Open the system phone app
  2. dial  *#*#2432546#*#*
    aka
    *#*#CHECKIN#*#*