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
*/

mercredi, juin 1 2011

Crash du serveur ASP.NET hébergeant les informations de trafic ZVV

Les transports publics zurichois – aussi appelés ZVV – ont équipé tous les bus, trams et trains de chouettes écrans donnant les informations quant aux prochains arrêts, les correspondances à cet arrêt etc.

Et comme tout système, cela peut crasher… comme dans ce cas dans un tram il y a quelques temps, où la page affichant les correspondances a du avoir un problème de “mémoire” (Attempted to read or write on protected memory) ;-)

DSC_0034

- page 1 de 2