lundi, mars 3 2014

Visualize and alter MP3 properties in Excel

In this article I want to show you how to quickly generate an Excel list of your MP3 files and their associated properties. In a second step, we also edit in mass these properties within Excel, so you can standardize your music library and have a consistent view if e.g. you expose it to other devices via e.g. DLNA.

1. Get a listing in Excel of all properties of your MP3s

Create an Excel file and don’t forget to save it with an .xlsm extension (Excel worksheet with macros). Add the following labels for each column in row 1:

  1. Full Path
  2. File name
  3. Artist
  4. Album
  5. Year
  6. Genre
  7. Title
  8. Artist 2
  9. TrackNr
  10. Duration
  11. Quality

Open the Visual Basic for Application Editor of Excel ([Alt] + [F11]), and add a new module. Insert the following code, which contains all the needed Excel VBA logic:

Dim line As Integer
' Dumps the properties of all the files within the given path into the current worksheet
Sub StartMp3Processing()
line = 2
dumpFileProperties "[PATH_TO_YOUR_MP3_ROOT_FOLDER]"
End Sub
Function UpdateMp3(file, field, oldValue, newValue)
If file <> "Full Path" Then
exe = "[PATH_TO_TAGUPDATE_BINARY]\TagUpdate.exe"
Shell exe & " " & SurroundWithQuotes(file) & " " & _
SurroundWithQuotes(field) & " " & _
SurroundWithQuotes(oldValue) & " " & _
SurroundWithQuotes(newValue)
End If
End Function
' TODO: enhance this - e.g. strip existing quotes with \
Function SurroundWithQuotes(value)
SurroundWithQuotes = Chr(34) & value & Chr(34)
End Function
' Recusively dumps specific properties of files present within the given path
Function dumpFileProperties(pathToAnalyse)
' List of the properties ID we want to dump
' A full list of available properties is available in the comments of
' http://msdn.microsoft.com/en-us/library/windows/desktop/bb787870(v=vs.85).aspx
propertiesIdList = Array(0, 13, 14, 15, 16, 20, 21, 26, 27, 28)
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(pathToAnalyse)
For Each strFileName In objFolder.Items
If strFileName.isFolder Then
dumpFileProperties strFileName
' Uncomment this line and comment the other Else if you want to restrict the dump...
'ElseIf objFolder.getDetailsOf(strFileName, 2) = "VLC media file (.mp3)" Or objFolder.getDetailsOf(strFileName, 2) = "VLC media file (.wav)" Then
Else
' Dump the full file path in the first column
ActiveSheet.Cells(line, 1) = strFileName.Path
col = 2
For Each propId In propertiesIdList
ActiveSheet.Cells(line, col) = objFolder.getDetailsOf(strFileName, propId)
col = col + 1
Next
line = line + 1
End If
Next
End Function

Update placeholder [PATH_TO_YOUR_MP3_ROOT_FOLDER] with the path to your MP3 files and, once done, press [F5] in the Visual Basic Editor to start sub “StartMp3Processing”. Wait a few minutes for the processing of the mp3 files to finish:

image

By splitting the window and freezing the panels, you can keep the headers always visible. Next to this, add the smart filters to the data and you’re now ready to process all the available data:

image

2. Edit the properties in Excel

Let’s now update inaccurate properties within Excel. Unfortunately, VBScript object Shell.Application only provides a read-only interface to get this data. To edit these properties, we have to rely on an external program. In this case, I decided to code my own little command line program in C#, which references the taglib sharp library. No compiled version of it is available on GitHub but you can download my compiled version here.

The code of the little command line program – TagUpdate.cs – is pretty simple (see below). It can be easily compiled on any recent Windows machine using command c:\Windows\Microsoft.NET\Framework\v3.5\csc.exe TagUpdate.cs /r:taglib-sharp.dll

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TagUpdate
{
class Program
{
// Created by Alexandre Herzog under a CC-BY-SA licence (Creative Commons Attribution + ShareAlike)
// You can compile this software on any Windows machine with the following command:
// C:\>c:\Windows\Microsoft.NET\Framework\v3.5\csc.exe TagUpdate.cs /r:taglib-sharp.dll
// Lists all the fields we can update with their function
private static Dictionary> dict =
new Dictionary>();
static void Main(string[] args)
{
if (args.Length != 4)
{
Console.WriteLine("Error while invoking TagUpdate - {0} arguments were passed instead of 4", args.Length);
Console.ReadLine();
return;
}
// Populate the list of available fields to update
dict.Add("Artist", checkAndUpdateArtist);
dict.Add("Album", checkAndUpdateAlbum);
dict.Add("Year", checkAndUpdateYear);
dict.Add("Genre", checkAndUpdateGenres);
dict.Add("Title", checkAndUpdateTitle);
dict.Add("TrackNr", checkAndUpdateTrackNr);
// sets the variables
string file = args[0];
string champ = args[1];
string oldValue = args[2];
string newValue = args[3];
try
{
UpdateMp3Tags(file, champ, oldValue, newValue);
}
catch (Exception ex)
{
Console.WriteLine("\n\nError {0} while updating file {1}:\n{2}\n\n", ex.Message, file, ex.StackTrace);
Console.ReadLine();
}
}
public static int UpdateMp3Tags(string file, string attribute, string oldValue, string newValue)
{
TagLib.File tlf = TagLib.File.Create(file);
if (!dict.ContainsKey(attribute))
throw new Exception(String.Format("No such attribute / action is defined:\nReceived: {0}\nPossible actions: {1}", attribute, FromArrToString(dict.Keys.ToArray())));
dict[attribute](tlf, oldValue, newValue);
return 0;
}
/*
*		Helpers
*/
private static string FromArrToString(string[] param)
{
if (param.Length == 0)
return "";
// Linq beauty :)
return param.Aggregate((current, next) => current + "; " + next);
}
private static string ReturnsEmptyIfNull(string txt)
{
if (String.IsNullOrEmpty(txt))
return "";
return txt;
}
private static uint? ReturnNullIfEmpty(string txt)
{
if (String.IsNullOrEmpty(txt))
return 0;
return Convert.ToUInt32(txt);
}
/*
*		Check and update functions for the various properties
*
*		All functions have the same signature to match the dictionary
*/
private static int checkAndUpdateAlbum(TagLib.File file, string oldValue, string newValue)
{
if (ReturnsEmptyIfNull(file.Tag.Album) != oldValue)
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", file.Tag.Album, oldValue));
file.Tag.Album = newValue;
file.Save();
return 0;
}
private static int checkAndUpdateArtist(TagLib.File file, string oldValue, string newValue)
{
if (FromArrToString(file.Tag.Artists) != oldValue)
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", FromArrToString(file.Tag.Artists), oldValue));
file.Tag.Artists = new string[] { newValue };
file.Save();
return 0;
}
private static int checkAndUpdateTitle(TagLib.File file, string oldValue, string newValue)
{
if (ReturnsEmptyIfNull(file.Tag.Title) != oldValue)
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", file.Tag.Title, oldValue));
file.Tag.Title = newValue;
file.Save();
return 0;
}
private static int checkAndUpdateYear(TagLib.File file, string oldValue, string newValue)
{
if (file.Tag.Year != ReturnNullIfEmpty(oldValue))
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", file.Tag.Year, oldValue));
file.Tag.Year = Convert.ToUInt32(newValue);
file.Save();
return 0;
}
private static int checkAndUpdateTrackNr(TagLib.File file, string oldValue, string newValue)
{
if (file.Tag.Track != ReturnNullIfEmpty(oldValue))
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", file.Tag.Track, oldValue));
file.Tag.Track = Convert.ToUInt32(newValue);
file.Save();
return 0;
}
private static int checkAndUpdateGenres(TagLib.File file, string oldValue, string newValue)
{
if (FromArrToString(file.Tag.Genres) !=oldValue)
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", FromArrToString(file.Tag.Genres), oldValue));
file.Tag.Genres = new string[]{newValue};
file.Save();
return 0;
}
}
}

Once compiled, you just have to replace placeholder [PATH_TO_TAGUPDATE_BINARY] in your Excel VBA code with the full path pointing to TagUpdate.exe and you’re ready to go. You can use this binary to update the following mp3 properties:

  • Artist
  • Album
  • Year
  • Genre
  • Title
  • TrackNr

To automate the update process and allow mass-modifications, use the previously defined custom Excel formula as below:

image

The first argument of UpdateMp3 is the full path to the MP3 file, the second is the type of information to update – here the artist’s name. The third value is the old value and the fourth the new value to set. Having to specify the expected old value of the file ensures you have a consistent set of data before the update and ensures that you’re not overwriting unwanted information.

mardi, janvier 28 2014

Random and safe password storage for local Windows admin accounts

Nowadays, it is considered best practice to generate unique local account passwords for each machine in a Windows domain. This applies typically for the local administrator user and often one of the three following approaches are taken:

  1. The password is generated using a reversible algorithm (e.g. SHA1(salt + machine name))
  2. The password is randomly generated and then stored on a central system
  3. The password is set via Group Policy Preferences (GPPs)

The first method has several advantages: your machine doesn’t need to be connected to a network to set the generated password and every administrator who has access to the implementation of the reverse algorithm can recover the password. The latter advantage is also the biggest issue: everyone able to guess or find the algorithm (e.g. by browsing the build share) can deduce the password of all machines. This process also violates Kerckhoffs's principle stipulating that only the key and not the algorithm should be kept secret.

The second method doesn’t rely on a secret algorithm, but introduces two major constraints: the machine and the central service where the randomly generated passwords are stored must be online and this service will likely become the target of a motivated attacker.

Finally, setting any password via GPPs is highly discouraged, as any domain user is able to decrypt these secrets.

Is there any other solution? I think there’s one, based on the following core ideas:

  • The whole setup should allow the algorithm to be public
  • Gaining access to the password storage location should not be considered as sufficient to recover the passwords
  • The element allowing a password recovery doesn’t absolutely need to be connected to a network

Based on this, I imagined a scheme based on public-key cryptography where a password gets randomly generated and then encrypted using the public key of a given certificate. The resulting blob can be stored wherever desired, as only the possession of the private key – held e.g. offline – will allow recovering the password.

This may sound hard to implement or manage, but I’m convinced it can be done fairly efficiently. Additionally, a password rollover could be organised via GPO every few months or so. As a proof of concept, a quick implementation of mine in .NET / C# that everyone (running Windows) can download, compile and test can be found below.

Have a look at this implementation and I’m looking forward to your feedback and critics regarding this other but in my opinion simple way to generate and store unique passwords.


Implementation PoC:

File SafePasswordGenerator.cs

using System;
using System.IO;
using System.Text;
using System.Diagnostics;
using System.Security.Cryptography;
using System.Security.Cryptography.X509Certificates;
class SafePasswordGenerator {
/*
Prototype for a safe password generation process for e.g. local Windows administrator accounts.
Created by Alexandre Herzog under a CC-BY-SA licence (Creative Commons Attribution + ShareAlike)
This work relies on two part:
1. This file which generates a safe password and encrypts it using the public key of a given certificate (see below)
2. File DecodePassword.cs which allows the decryption of the encrypted string
To save the password in an encrypted form, you just need the public key of the certificate saved in file pub_cert.cer
To decrypt the saved encrypted password, you need the private key of the certificate saved in file private_cert.pfx
A self-signed certificate can be generated using standard Windows tools:
1. Create template template.inf using the following (adapted) template
[NewRequest]
Subject = "CN=W2K8-BO-DC.contoso2.com"
RequestType = Cert
ExportableEncrypted = TRUE
KeyLength = 2048
KeySpec = AT_KEYEXCHANGE
2. Generate the certificate with command
certreq -new template.inf
3. Don't bother with the popup, the self-signed certificate is already in your store
(cf http://serverfault.com/questions/413976/can-you-generate-a-self-signed-certificate-on-windows-server-using-cli-tools-lik)
4. Export the certificate from your windows store (certmgr.msc - personal - certificate)
Compile this program with command
c:\Windows\Microsoft.NET\Framework\v2.0.50727\csc.exe SafePasswordGenerator.cs
Refs:
- http://technet.microsoft.com/en-us/library/dn296456.aspx (CertReq reference)
- http://msdn.microsoft.com/en-us/library/system.security.cryptography.rsacryptoserviceprovider.encrypt.aspx
- http://msdn.microsoft.com/en-us/library/system.security.cryptography.x509certificates.publickey(v=vs.110).aspx
*/
private static Encoding asciiEncoding = Encoding.ASCII;
// Definition of 64 possible characters
private static char[] possiblePasswordChars = new char[] {'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','0','1','2','3','4','5','6','7','8','9','-','_'};
private static RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
private static bool useOAEP = true; // RSA padding (should) avoid an attacker being able to brute force passwords and see if he gets the matching cipher text
// Certificate with only a public key, used to encrypt the password
private static X509Certificate2 cert = new X509Certificate2("pub_cert.cer");
// Text which will be encrypted: {0} is a placeholder for the machine name, {1} a placeholder for the password
const string savedText = "Password for machine '{0}': {1}";
public static void Main(string[] args) {
// Setting password to 16 chars to avoid any LM-hash related issue...
String password = GetRandomString(16);
string strToEncrypt = String.Format(savedText, Environment.MachineName, password);
string base64String = Convert.ToBase64String( Encrypt(strToEncrypt) );
Console.WriteLine(String.Format("String '{0}' gets encrypted to base64 representation '{1}'", strToEncrypt, base64String));
// Uncomment to test decryption
// Don't forget to hardcode the PIN for test purposes in method DecodeMe(byte[] b)
//Console.WriteLine("Decrypted value: {0}", DecodeMe(base64String));
/* Uncomment to store this information somewhere */
/*
// Stores the encrypted value on a network share
File.AppendAllText(
Path.Combine(@"\\network\share$\localAdminLog\", String.Format("machine_{0}.txt", Environment.MachineName)),
String.Format("{0}: {1}", DateTime.Now, base64String)
);
// Saves the password in a environment variable for this execution context
Environment.SetEnvironmentVariable("generatedPassword", password);
// Sets the given password for the local administrator user
Process.Start("cmd.exe", String.Format("net user administrator {0}", password));
*/
}
private static byte[] Encrypt(string text) {
// TODO/NEXTV check if we can rely on ECC!
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;
byte[] byteToEncrypt = asciiEncoding.GetBytes(text);
return rsa.Encrypt(byteToEncrypt, useOAEP);
}
private static string GetRandomString(int length) {
byte[] random = new byte[length];
rng.GetBytes(random); // The array is now filled with cryptographically strong random bytes
char[] randomStr = new char[length];
for (int i=0; i < length; i++)
randomStr[i] = possiblePasswordChars[random[i] % possiblePasswordChars.Length];
return new String(randomStr);
}
// Not needed for the encryption process itself.
private static string DecodeMe(string txt)
{
byte[] decrypted = DecodeMe(Convert.FromBase64String(txt));
return String.Format("Decrypted string is below:\n{0}", asciiEncoding.GetString(decrypted));
}
// Not needed for the encryption process itself.
private static byte[] DecodeMe(byte[] b)
{
X509Certificate2 privateCert = new X509Certificate2("private_cert.pfx", "");
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)privateCert.PrivateKey;
return rsa.Decrypt(b, useOAEP);
}
}

File DecodePassword.cs

using System;
using System.Text;
using Microsoft.VisualBasic;
using System.Security.Cryptography;
using System.Security.Cryptography.X509Certificates;
class DecodePassword {
/*
Prototype for a safe password generation process for e.g. local Windows administrator accounts.
Created by Alexandre Herzog under a CC-BY-SA licence (Creative Commons Attribution + ShareAlike)
This work relies on two part:
1. File SafePasswordGenerator.cs which generates a safe password and encrypts it using the public key of a given certificate (see below)
2. This file which allows the decryption of the encrypted string
Compile with
c:\Windows\Microsoft.NET\Framework\v2.0.50727\csc.exe DecodePassword.cs /reference:Microsoft.VisualBasic.dll
*/
private static bool useOAEP = true;
public static void Main(string[] args)
{
Encoding asciiEncoding = Encoding.ASCII;
// using VB's Interaction, as we all miss these nice looking GUI components!
String certPassword = Interaction.InputBox("Enter the password of your private certificate:", "DecryptPassword", "", -1, -1);
X509Certificate2 cert = new X509Certificate2("private_cert.pfx", certPassword);
// No Console.ReadLine as the copy-paste tends to be truncated...
String strToDecrypt = Interaction.InputBox("Enter the string to decrypt:", "DecryptPassword", "", -1, -1);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PrivateKey;
byte[] decrypted = rsa.Decrypt(Convert.FromBase64String(strToDecrypt), useOAEP);
Console.WriteLine("Decrypted string is below:\n{0}", asciiEncoding.GetString(decrypted));
Console.ReadLine();
}
}
/*
Random string 'Password for machine '[...]': 34-PNxSstfSzZBAq' gets encoded to '
BT[...]lylhw=='
========================
Decrypted string is below:
Password for machine '[...]': 34-PNxSstfSzZBAq
c:\>DecodePassword.exe
Decrypted string is below:
Password for machine '[...]': 34-PNxSstfSzZBAq
*/

samedi, janvier 25 2014

Hash of a file using a built-in Windows tool

Calculating or verifying the hash of a file should be a common task to ensure file integrity - e.g. a file you just downloaded over a http connection. Unfortunately, there is no well-known tool under Windows to perform such a task, and most forums suggest writing their own PowerShell script, C# program or download further third party tools.

I recently stumbled across a little known but powerful tool shipped by Windows (at least since Windows 7, probably since Vista), which performs exactly this task: certutil:

certutil -hashfile <file> [MD5|SHA1|SHA256|SHA512]

Usage example – compute the SHA512 hash of a given file

C:\temp>certutil -hashfile iso_dl_over_http.iso SHA512
Hachage SHA512 du fichier iso_dl_over_http.iso :
f7 fe 44 29 17 9b dd 4c d3 01 77 ce 66 39 60 f1 53 bb 5d dd 8d 22 7b ef b6 69 29
28 16 38 ff bb a6 58 57 b4 b4 3a 60 65 dc 96 23 8a 2a 0a 9e 5f ca c4 fe 40 ec 9
8 74 05 f9 95 6f 78 bd 7f 7e 15
CertUtil: -hashfile La commande s'est terminée correctement.

samedi, janvier 4 2014

Statistics about blog comments spam

A little less than one year ago, I drew a few statistics on the spam I was getting on this blog. Since then, the number of spammy comments continued to increase as shown in the graph below:

image

The email regex rule documented in the previous blog post (called in this graph “dcFilterAlexAntispam) completes the other anti-spam measures and helps catching 100% (!) of all unwanted comments so far. This obviously raises additional questions, as it hints that one operator or software is behind all this spam.

Due to this single software in usage, can we easily identify its source? Let’s break down the collected IP address to their /8 mask and see if we find any obvious source:

image

A few networks come out but we are far from the Pareto rule where we could attempt to eliminate 80% of the spam by blacklisting 20% of the offending IP addresses.

But maybe we can correlate IP addresses to countries or providers? Let’s use for this the IP to AS service of Team Cymru. But first, let’s aggregate the IP addresses to avoid sending duplicates or multiple IP addresses within the same /24 range. Of the little less than 20’000 entries, 6844 unique /24 IP addresses were identified. Let’s save this list in a text document, insert keywords “begin” and “verbose” at the start of the document and insert “end” at the end of it before invoking the whois based conversion service:

$ netcat whois.cymru.com 43 < asm_requests.txt | sort -n > asm_responses.txt

During the import in your preferred spreadsheet, don’t forget to trim away the various whitespaces of the fields. The outcome is pretty interesting, as some countries emerge from the statistic:

image

Drilling down to the top 10 countries, we get the following representation:

image

While some countries are consistently spamming a lot over the year, other have peaks – e.g. Germany in the third trimester of 2013 or Sweden in first trimester of 2013.

There are of course some limitations with this evaluation. First of all, the link between IP address and AS ownership was established today, while some of the IP addresses were recorded over a year ago. This IP address might have been owned by a less reputable source than now and thus induce a bias. Furthermore, some AS might register themselves where their headquarters is, despite being located all over the world. This could help explain  the predominance of e.g. the US in this statistic.

lundi, février 25 2013

Nonogram game in SVG

While my summer project of 2012 has been up for a while now, I did not write much about it nor publish the URL to this nongram game written in HTML/SVG/JavaScript:

image

Unlike many other versions, I did not want to involve any Flash but rely on 100% on an HTML stack. The game works well in Chrome and Firefox, despite a few issues for the latter.

The whole was written purely with Notepad++ and intermittent Internet access, making the whole process longer but much more interesting, as I had to solve many issues by myself without immediately googling for help. In this regard, the development tools of Chrome have proven to be essential, allowing dynamic inspection of the HTML as well as of the SVG components.

There is almost no JavaScript in the HTML page as you can see in view-source:www.ness.ch/nonogram/. All the funny stuff is (somehow) available in https://www.ness.ch/nonogram/script.js?v=0.2. Disabling the right click within the HTML was not done to avoid people stealing a source, but to avoid the contextual menu in the SVG grid part of the page.

Further details on how to play are available on the page itself:

image

Hope you enjoy playing one of the over 20 different nonogram grid setups!

- page 1 de 15