Remove Unused References with Visual Studio 2017

I liked the Reference Assistant extension very much, but unfortunately it only works for Visual Studio versions 2010 through 2013, and I always wanted to adapt the extension to later versions of Visual Studio.

Now I did.

So I forked the original code from the repository on GitHub (really originally hosted on CodePlex).

Since I had no experience in working with the VSSDK, I created a dummy project and clicked my way through it, and tried to figure out, where the original code hooked into VS, and how to migrate that code from 2010 to 2017.

One of the main obstacles was the setup of the Options page, which seems to have changed fundamentally. Fortunately, the Extensibility Samples also contain a project named Options covering the Tools->Options dialog.

Of course, migration also means upgrading .Net versions (implied by creating a VS2017 extension project), replacing assembly references with NuGet packages, and a little bit of tuning and tweaking.

You can find the upgraded Reference Assistant on GitHub, the first release version is here.

What I like about this tool is that is lets you preview the changes it is going to make, and exclude references from removal you know are required, as the tool does not consider occasional implied references and assemblies referenced in .config files.

Importing mysqldump into MS SQL Server

To import data from MySQL into a MS SQL database, we need to first analyze the output of mysqldump, the backup utility of MySQL. I have covered the most obvious differences to T-SQL in my previous post.

Fortunately, the output of mysqldump is line-oriented, i.e. every CREATE TABLE statement, table column or index, and INSERT statement is in a separate line.

In a brute-force approach, we can therefore parse each line using regular expressions, and do not require full parsing involving tokenizer and grammar.

I found a couple of interesting points that need to be considered when generating T-SQL statements:

  • As T-SQL did not support “DROP IF EXISTS” before SQL Server 2016, we need to provide the classical variant “IF OBJECT_ID() IS NOT NULL”
  • NVARCHAR(MAX) columns cannot be indexed
  • BINARY data needs to be 0x-encoded
  • UNIQUE INDEXes should not include NULLable columns
  • Multi-row INSERT INTO statements cannot contain more than 1000 rows
  • ‘0000-00-00’ is a valid date in MySQL, but not in MSSQL. The value is being replaced by ‘1800-01-01’ to avoid the error message

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Some indexes may require manually editing column collations, e.g. for unique case-sensitive MySQL indexes.

When executing T-SQL statements in SSMS, a number of warnings or errors can occur:

  • A warning that indexed data is supported to a maximum length of 960 bytes
  • Inserting data may result in the error “String or binary data would be truncated.”
  • Some files are too big to execute in SSMS, and need to be executed using sqlcmd
  • A batch with too many too large INSERT INTO statements may raise the error

    There is insufficient system memory in resource pool ‘default’ to run this query

    so we break up the batch using “GO”

  • Multiple cascade paths

MS SQL Server does not support ON DELETE CASCADE clauses such that the grandchild record of a parent-child-grandchild relation cannot be uniquely identified. Creating such a FOREIGN KEY raises the error message

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint ‘fk_xxx’ on table ‘xxx’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

In such cases, either drop the ON DELETE CASCADE if possible, and replace the deletion cascade with an INSTEAD OF DELETE trigger.

I developed mysqldump2mssql, a prototype implementing these rules, which is available on my GitHub. The program has been developed and tested against MySQL dumps of MediaWiki, Joomla, Bugzilla and Nextcloud.

The tool generates up to 4 files for each table defined in the source SQL file:

  • create.sql: the CREATE TABLE statement, optional CREATE UNIQUE INDEX statements
  • data.sql: the INSERT INTO statements
  • fk.sql: the FOREIGN KEY relations to other tables
  • fulltext.sql: the CREATE FULLTEXT statement

The filenames of the generated files consist of the sequence number of execution, the table name, and one of the endings mentioned in above table.

Parsing mysqldump files

MySQL and MS SQLServer both can be accessed using SQL, but if you look closely at both SQL dialects, you’ll find a lot of differences.

Backups of MySQL databases are created using the utility mysqldump, which simply dumps all the SQL statements required to recreate the database into a SQL file, as opposed to SQLServer backup, which creates a binary file representing the database contents.

As we are interesting in parsing the SQL and converting it into valid T-SQL statements, we have to look at the differences between the SQL dialects. Fortunately, since the SQL files are output by a tool, the generated statements are fairly regular.

Here are some special features of the MySQL dialect, which need to be handled for conversion into T-SQL:

  • Identifiers are quoted with a backtick `, rather than square brackets
  • IDENTITY columns are marked by the auto_increment keyword
  • Binary columns may store text values
  • Text values may contain character codes with leading backslash \, as known from C# or C
  • Values for DATETIME, DATE and TIME columns need not be quoted as strings
  • ‘0000-00-00’ is a valid DATE
  • A CREATE TABLE statement may include the definition of indexes, unique indexes and fulltext indexes
  • Both SQL engines provide support for collations, but they are completely different

I have previously written about converting MySQL SQL into T-SQL manually, but having a tool perform the repetitive actions would be very nice 😉

Redirection Attack in ScrewTurnWiki Administration

I installed the latest verion of ScrewTurnWiki 5, first to obtain a valid database schema, and second to click through the Administration menu to figure out what can be configured in what I considered the last “official” edition of STW.

Unfortunately, I can only find the binary release, as opposed to all other versions of ScrewTurnWiki that come also with source code (or only with source code).

So I entered the Administration menu and clicked through the menu items, when suddenly a click on Global Admin Home redirected me to an ad site, and away from STW.

After a bit of research the code, it turned out that the version update check tries to retrieve information on whether a newer software version exists, but handles the result of the HTTP request incorrectly: The resulting HTML is simply copied into the admin back-end. Technically speaking, the HTML is passed through in an <asp:Literal>.

For version 5, the update check calls the URL http://www.sunhorizon.info/Version1/Wiki/1.htm, which was once a domain hosting STW, but now abandoned.

The request returns

<html><head><title>Loading…</title></head><body><script type=’text/javascript’>window.location.replace(‘http://www.sunhorizon.info/Version1/Wiki/1.htm?js=…&sid=…guid…&#8217;);</script></body></html>

and it is obvious that embedding this content as literal HTML immediately performs a Javascript redirect with tracking parameters js= and sid=, and off it goes to whatever ad netware is being lucky.

The easiest workaround to solve this redirection attack is to locate the AdminGlobalHome.aspx file, and modify the <asp:Literal ID=”lblSystemStatusContent”> element to include a property Mode=”Encode” to disable harmful HTML:

<asp:Literal ID="lblSystemStatusContent" runat="server" 
  meta:resourcekey="lblSystemStatusContentResource1" 
  Mode="Encode" />

or to set Visible=”false”.

Versions 3 and 4 seem to query http://www.screwturn.eu/Version/Wiki/3.htm and http://www.screwturn.eu/Version4.0/Wiki/4.htm, also an abandoned STW domain, but without Javascript redirection.

 

AutoMySQLBackup Warning

When I replaced my original Ubuntu server with the (then) more current Ubuntu 18.04, I also moved MySQL databases and wanted to have them backed up regularly.

I had used the script AutoMySQLBackup which did the job fine, and installed it on the new machine. (When I first found the script, I adapted for PowerShell on Windows it to backup my SQL Server databases)

On the new machine, however, the tool mysqldump issued the warning

[Warning] Using a password on the command line interface can be insecure.

The change seems to have been introduced in MySQL 5.6, and the solution to the warning is documented in the MySQL 5.6 Reference Manual. There are also answers on SO regarding the warning

mysql_config_editor set --login-path=local --host=localhost --user=username --password

mysql --login-path=local -e "statement"

 

I guess I tried to change the .sh script to call mysqldump with –login-path instead of –user/–password.

As the original script is not maintained anymore, I found this fork of AutoMySQLBackup which is still active, and also documents the use of –login-path.

Visualizing ECDC’s COVID-19 data

The ECDC (European Centre for Disease Prevention and Control) is providing world-wide data on Coronavirus infections and deaths.

The data format changed a couple of times, beginning with date-stamped Excel files (.xlsx), then .csv files.  then the column names  and as of today, no more .xlsx files are provided.

(I initially tried to analyze the .xlsx files with SheetJS, but always got the error message

Cannot find file [Content_Types].xml in zip

which I could not solve, therefore switching from .xlsx to .csv).

As file hosting moved to opendata.ecdc.europa.eu, column names changed (at least in the .csv), and the date has been removed from the filename.

So I started out with a little pen on CodePen.io (data as of March 25), consisting of loading data from .csv, selecting countries of interest, and the kind of data to be displayed in a Chart.js chart:

  • Number of cases, or number of deaths
  • Count per day (as directly from the data files); total sum per day; and change in cases per day, optionally averaged over a number of days

After experimenting with the pen, I exported it and created the full HTML page containing the original pen, and adjusting a couple of things.

Next, I created a Scheduled Task to fetch the latest ECDC data and store the file on the webserver. (Originally I had a problem with fetching the file directly in Javascript because the webserver did not provide a Access-Control-Allow-Origin header)

You can find the most current version of my Covid visualization on my website.