Rob Kraft's Software Development Blog

Software Development Insights

Archive for March, 2020

How to Write Multiple Rows To A Google Sheet From C# .Net

Posted by robkraft on March 26, 2020

Google provides good documentation to help you get started reading from and writing to Google sheets using their .Net SDK here: https://developers.google.com/sheets/api/guides/values

But I didn’t find an example there or elsewhere about how to add or update multiple rows in a single API call to Google Sheets.  I figured out how to do it and am sharing here.

private static void InsertToSheet(UserCredential credential, List people)
{
	SheetsService service = new SheetsService(new BaseClientService.Initializer()
	{
		HttpClientInitializer = credential,
		ApplicationName = ApplicationName,
	});
	string sheet = "People";
	String spreadsheetID = "1sRHWW2fynHZoQl__yourspreadsheetid__wwxfDuMUM";
	var range = $"{sheet}!A2:C51";
	var valueRange = new ValueRange();
	valueRange.Values = new List<IList>();
foreach (var person in people)
	{
		var objectListthis = new List() { person.FirstName, person.LastName, person.BirthDate };
		valueRange.Values.Add(objectListthis);
	}
	var appendRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetID, range);
	appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
	var appendResponse = appendRequest.Execute();
}
public class Person
{
	public string FirstName { get; set; }
	public string LastName { get; set; }
	public string BirthDate { get; set; }
}

The trick is to define valueRange.Values as a List, then add each or your rows to that list one at a time as shown.  When specifying the range I started with row 2 (A2:C51) because my first row has headers, and I go through row 51 because I am always inserting exactly 50 rows of data.  Also notice that in my example I am overwriting rows doing an “Update” instead of adding new rows via an “Append”.

Posted in Coding | Leave a Comment »

How to Emulate Azure Function Application Settings Read Via Environment.GetEnvironmentVariable on Local Machine

Posted by robkraft on March 25, 2020

I borrowed a bit of code from an existing .Net Framework app that I had used to write an Azure function years ago.  But my new app was based on .Net Core and the code to retrieve an Environment variable (Environment.GetEnvironmentVariable) did not pull a value out of my .json file.

It took me a little googling around to figure out how to make it work so I figured I might as well share my findings in a blog post.  It will probably help “Future Rob” fix the problem more quickly if no one else.

If you have code like the following that pulls values from the “Application Settings” in an Azure function:

data[“refresh_token”] = Environment.GetEnvironmentVariable(“refresh_token”);
data[“grant_type”] = “refresh_token”;
data[“client_id”] = Environment.GetEnvironmentVariable(“client_id”);
data[“client_secret”] = Environment.GetEnvironmentVariable(“client_secret”);

but you want to run that code on your local machine, you just need to put your values inside the “Values” node of local.settings.json as shown here:


{
"IsEncrypted": false,
"Values": {
"AzureWebJobsStorage": "UseDevelopmentStorage=true",
"FUNCTIONS_WORKER_RUNTIME": "dotnet",
"client_id": "xxx",
"client_secret": "yyy",
"refresh_token": "zzz"
}
}

 

Posted in Coding | Leave a Comment »

How To Add Azure “Storage Emulator” to an Existing Azure Function Running on your Local Machine

Posted by robkraft on March 24, 2020

I created a new Azure Function App on my local machine in Visual Studio 2019 today.  One of the wizard steps gave me the option to use “Storage Emulator” for storage account and I decided to choose no storage.

StorageEmulator

That was a mistake because I also set my Azure Function App up to run based on a Timer Trigger, and in order to run it locally I need that Storage Emulator.

So I created another Visual Studio Azure Function App and chose the Storage Emulator so I could see what it generated differently, and to my surprise there were no additional dependencies, just one value that needs to be added to local.settings.json to make it work.

“Values”: {
“AzureWebJobsStorage”: “UseDevelopmentStorage=true”,

Once I replaced the empty string value for AzureWebJobsStorage with UseDevelopmentStorage=true, the app ran locally with no problems.

StorageEM2

Posted in Coding | Leave a Comment »

How To Fix Scaling Problem for High DPI PC Running Kali Linux

Posted by robkraft on March 11, 2020

I couldn’t find any posts on the web telling me the best way to fix this problem so I am sharing my solution here.

The problem I experienced is that I downloaded Kali Linux to run in VMPlayer on my High DPI Windows 10 machine but the guest font and screen is too small and hard to read to be really usable.  VMPlayer doesn’t have any settings to fix this, but you can change the VMPlayer.exe settings in the Windows host OS to fix it.

Find your VMPlayer.exe, probably in a folder like C:\Program Files (x86)\VMware\VMware Player.  Right-click on it, select Properties, Compatibility Settings.  Then click on the “Change high DPI settings”.

I checked the checkbox labeled “Use this setting to fix scaling problems for this program instead of the one in Settings”.

I also checked “Override high DPI Scaling behavior.  Scaling performed by System”.

When I restarted VMPlayer and opened Kali Linux, the UI was much more functional and usable, once I opened it full screen.

Posted in I.T. | Leave a Comment »

Free Tool from F-Secure To Check If Your Password Was Compromised

Posted by robkraft on March 11, 2020

F-Secure has an easy online tool you can use to find out what sites leaked your email address, password, and other information.

The URL is: https://www.f-secure.com/en/home/free-tools/identity-theft-checker

F-Secure is a site I trust and recommend.  Just enter your email address and within minutes you will receive an email with information about each breach your email address was part of.

F-Secure has other tools at https://www.f-secure.com/en/home/products#free such as a tool to show your IP address, a tool to check your router for flaws, and an online virus scanner for your PC.

Posted in Home Tech, I.T. | Leave a Comment »

My Favorite New Interview Question To Ask a Potential Employer

Posted by robkraft on March 5, 2020

I am not in a rush to find a new job and plan to look carefully at each potential employer to see if I think the culture of that company is a good fit for me.  But how can we get a good grasp of the culture without working at the company for a while?  I have been in interviews where a candidate asks, “What is the culture like here?”, but responses to the question are ambiguous because it is difficult to define culture and to accurately guess what the questioner is concerned about when they ask about culture.

The question I like to ask to assess culture is more direct.  That question is:

“Have any of the current managers in the company ever yelled at any of the employees?”

I know what you are thinking.  Wow, that is a bold question!  But it partially gets to the heart of the issue.  I want to know that the managers are emotionally mature.  I believe that an emotionally mature manager will never yell at employees.  Perhaps I would expect yelling if I was joining the military.  And perhaps I should expect it in some industries; but I am a software developer and I do not expect it in the software development industry.  If yelling happens, and especially if it is common within the company, then I already know that is not a place where I want to work.

I believe that it is a little rude to spring this question upon my possible employer during the interview, which is why I like to submit this question to the potential employer before I meet with them in person.  In fact, I like to submit all of my questions to the employer in advance to give them time to prepare answers to those questions for me.

Do you have a better question to ask to determine if the company culture is right for you?

Posted in Culture | Leave a Comment »

C# .Net SQL Injection Detection – Especially for Legacy .Net Code

Posted by robkraft on March 4, 2020

When you have an existing .Net code base full of SQL statements, and you want to reduce the chance that there are SQL injection risks in the code, you may decide to perform a review of every SQL statement in order to confirm that they are all coded correctly; or you may hire another company to do this for you. But one problem with this approach is that the code is only “SQL Injection Free” from the moment the review is completed until people start modifying and adding to the code again.

What you should strive for is a way to make sure every past and future SQL statement gets tested for SQL Injection risk before it runs.  That is what this sample code provides you.  If you follow the patterns described here, I believe you can significantly reduce the risk that your code has bugs leading to SQL Injection and it will stay that way going forward.

Using the Decorator Pattern to Provide a Place To Add SQL Injection Detection

The primary technique I recommend in this article for adding SQL Injection detection into your application is to stop using the .ExecuteReader and .ExecuteNonQuery methods.  Instead, use the Decorator pattern to create your own method to be called in place of those two, and that method will include code to do some SQL Injection detection.

Replace:

SqlDataReader reader = command.ExecuteReader();

With 

SqlDataReader reader = command.ExecuteSafeReader(); //provided in sample code

The sample code provided behaves like the Proxy pattern in that it will make the actual call to the database after finding no SQL Injection risk.  The benefit of this approach is that you can then regularly scan your entire code base for the use of .ExecuteReader and .ExecuteNonQuery knowing that there should be no cases of those methods, other than the exception cases you expect.  Thus you can be sure that the majority of your code is running through your SQL Injection detector.

Another benefit of using the Decorator pattern to implement SQL Injection Detection is that you can also easily add other features such as:

  • Logging every SQL that is executed
  • Logging and blocking every SQL that is a SQL Injection risk
  • Altering every SQL on the fly.  One scenario where this could be helpful is that if you renamed a table in the database but had a lot of SQL that needed to change.  You could possibly add a find/replace to every SQL on the fly to change the table name, allowing you more time to find and correct all stored SQL fragments with the old table name.
	public static SqlDataReader ExecuteSafeReader(this SqlCommand sqlcommand)
	{
		if (!sqlcommand.CommandType.Equals(CommandType.StoredProcedure))
		{
			var sql = sqlcommand.CommandText;
			//Options: You could Add logging of the SQL here to track every query ran
			//Options: You could edit SQL - for example if you had renamed a table in the database
			if (!ValidateSQL(sql, SelectRegex))
				return null;
		}

		return sqlcommand.ExecuteReader();
	}

The SQL Injection Detection Code

Warning!  This does not detect all forms of SQL Injection, but it will detect most of them.  Here is what causes the class to throw an exception:

  • Finding a single apostrophe (single quote) that does not have a matching single apostrophe (single quote)
  • Finding double quotes that do not have a matching double quote.  This is only needed if the SQL Server has SET QUOTED_IDENTIFIER OFF.  However, you may also want to use this if your database is MySQL or some other DBMS.
  • Finding a comment within the SQL
  • Finding an ASCII value great than 127
  • Finding a semicolon
  • After extracting the strings and comments, finding any of a specific configurable list of keywords in a SELECT statement such as DELETE, SYSOBJECTS, TRUNCATE, DROP, XP_CMDSHELL

The code is written to be easy to change if you don’t want to enforce any of the rules above, or if you need to add similar rules because you have a special scenario or a DBMS besides SQL Server.

The code uses the regex [^\u0000-\u007F] to reject the SQL if it contains any non-ASCII characters.  This works for the applications I have written, but may need alteration for non American English language support.

The code also uses regexes to check SQL statements for undesirable keywords.  One regex is for SELECT statements and therefore blocks them if they contain INSERT, UPDATE, or DELETE.  Other keywords that may indicate a SQL Injection attempt are also rejected and that list includes waitfor, xp_cmdshell, and information_schema.  Note that I also include UNION in the list; so if you use the UNION keyword you will need to remove that from the list.  UNION is frequently used by hackers attempting to perform SQL Injection.

private static void LoadFromConfig()
{

	_asciiPattern = "[^\u0000-\u007F]";
	_selectpattern = @"\b(union|information_schema|insert|update|delete|truncate|drop|reconfigure|sysobjects|waitfor|xp_cmdshell)\b|(;)";
	_modifypattern = @"\b(union|information_schema|truncate|drop|reconfigure|sysobjects|waitfor|xp_cmdshell)\b|(;)";
	_rejectIfCommentFound = true;
	_commentTagSets = new string[2, 2] { { "--", "" }, { "/*", "*/" } };
}

SQL Server supports two techniques to comment out SQL code in a SQL Statement, two dashes, and enclosing the comment in /* */.  Since it is unlikely that developers write SQL to include comments, my default choice is to reject any SQL containing those values.

Exactly How Is The SQL Injection Detected?

There are basically three steps in the SQL Injection detection process.

First, the code checks for any ASCII values above 127 and rejects the SQL if one is found.

Second, the code removes all the code withing strings and comments.  So an SQL that starts out looking like this:

select * from table where x = ‘ss”d’ and r = ‘asdf’ /* test */ DROP TABLE NAME1 order by 5

becomes this:

select * from table where x = and r = t DROP TABLE NAME1 order by 5

Third, the code looks for keywords, like “DROP” and “XP_CMDSHELL”, in the revised SQL that are on the naughty list.  If any of those keywords are found, the SQL is rejected.

Formatting Methods included in the SQLExtensions Class

The SQLExtensions class provides additional methods to help your coders reduce the risk of SQL Injection.  These methods help coders format variables in SQL when doing so with a parameter is not an option.  The most useful of these methods is FormatStringForSQL and it could be used as shown here to enclose a string in SQL quotes as well as replace any single quotes contained within the value with two single quotes.


string sql = "select * from customers where firstname like " + nameValue.FormatStringForSQL();

Another advantage of using a method like this is that it makes it easy for you to change how you handle the formatting of strings everywhere within your code if you discover that you need to make a change.  For example, perhaps you decide to move your application from SQL Server to MySQL and therefore that you also need to replace double quotes in addition to single quotes.  You could make the change within this method instead of reviewing your entire code base to make the change one by one for each SQL.

Custom .Net Exception Class

I also provided a custom Exception primarily to show how easy it is to implement custom exceptions and because I think it is useful for this extension class.  This provides you more flexibility for handling exceptions.  You can catch and handle the exceptions raised specifically due to SQL Injection risk different than exceptions thrown by the underlying ADO.NET code returned from the database.


[Serializable]
public class SQLFormattingException : Exception
{
	public SQLFormattingException() {}

	public SQLFormattingException(string message): base(message) {}
}

The Rules For Detecting SQL Injection are Configurable

I made enabling/disabling configuration of the SQL Injection detections easy to change so that you could import those rules at runtime if desired so that different applications could have different rules.  Perhaps one of your applications needs to allow semicolons in SQL but the others don’t.  It is a good practice to implement the most stringent rules you can everywhere you can.  Don’t implement weak SQL Injection detection rules everywhere because a single place in your code needs weaker rules.  The rules are “Lazy Loaded” when needed, then cached, to support the ability to change them while an application is running by calling the InvalidateCache method provided.

Below is an example of one of the rules.  You can configure your code to reject the SQL if it contains SQL Server comments.


#region RejectComments Flag
private static bool? _rejectIfCommentFound = null;
public static bool RejectIfCommentFound
{
	get
	{
		if (_rejectIfCommentFound == null)
		{
			LoadFromConfig();
		}
		return (bool)_rejectIfCommentFound;
	}
}
#endregion

Steps To Implement and Use This Code

I suggest you take the following steps to implement this class:

  1. Get the SQLExtensions.cs class file into a project in your code base. You will also need the CustomExceptions.cs class file.  The program.cs just contains a sample usage and there is also a UnitTest1.cs class.
  2. Comment out all the lines in ReallyValidateSQL except for the “return true”
  3. Do a find and replace across your entire code base to replace ExecuteReader with ExecuteSafeReader
  4. Compile and test.  Your app should still work exactly the same at this point.
  5. Review the Customizable Validation Properties and decided which ones you want to implement, then uncomment the lines you commented out in ReallyValidateSQL
  6. Decide if you need to and want to replace dynamically constructed SQL in your application with any of the four FormatSQL… extension methods provided.
  7. Provide me feedback

MIT FREE TO USE LICENSE

This code has an MIT license which means you can use this code in commercial products for free!

A link to the source code example is here: https://github.com/RobKraft/SQLInjectionDetection

Posted in Code Design, CodeProject, Coding, Security | 2 Comments »