Getting resulting SQL string from parametric ADO.net SqlCommand

Microsoft is suggesting to always use Named parameters with SqlCommands for security reasons (to avoid SQL injection exploits), but they haven’t thought of providing a property at SqlCommand to give you back the result SQL so that you can have your app log it without resorting to SQL Server for that logging.

Found a solution at CommandAsSQL project on GitHub and the discussion that brought it to life at https://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object/

That solution was only for the case where the type of the SqlCommand was StoredProcedure, but needed to cover non-Stored procedures too (aka type Text of SqlCommand), so I augmented that library with this logic:

    private static void CommandAsSql_Text(this SqlCommand command, 
                                          System.Text.StringBuilder sql)
    {
        string query = command.CommandText;

        foreach (SqlParameter p in command.Parameters)
            query = Regex.Replace(query, 
               "\\B" + p.ParameterName + "\\b", p.ParameterValueForSQL());

        sql.AppendLine(query);
    }

The pull request is at: https://github.com/jphellemons/CommandAsSql/pull/3/commits/527d696dc6055c5bcf858b9700b83dc863f04896

The Regex idea was based on @stambikk’s and EvZ’s comments at that StackOverflow thread and the "Update:" section of https://stackoverflow.com/a/2544661/903783 that mentions "negative look-behind assertion".

The use of \B instead of \b for word boundary detection at the start of the regular expression is because the p.parameterName will always start with a "@" which is not a word character.

Note that ParameterValueForSQL() is an extension method defined at the CommandAsSql library to handle issues like single-quoting string parameter values etc. It may have a bug with its date handling currently though, will submit an issue on that

Btw, other promising piece of code is at https://github.com/jeroenpot/SqlHelper/blob/master/Source/Mirabeau.MsSql.Library/SqlGenerator.cs (mentioned at an answer in this thread). Probably could merge code from CommandAsSql and SqlGenerator if one finds something not working at one or the other.

Advertisements

Gotcha: VBScript DropHandler doesn’t support Unicode filenames

It seems VBScript (.vbs) script file’s DropHandler on Windows is broken regarding Unicode filenames, as described at https://stackoverflow.com/a/4366906/903783

A workaround suggested by user DG there (https://stackoverflow.com/a/13587850/903783) was to use a batch file to accept the dropped file, then pass the filename(s) to the VBScript script file (.vbs) as command-line parameters. If you just care about a single instead of multiple dropped files, there’s a simpler version of a batch file one can use, as I suggested at https://stackoverflow.com/a/52239049/903783. Copying below:

---

Based on DG’s answer, if you just want to accept one file as drop target then you can write a batch file (if you have it named as "x.bat" place VBscript with filename "x.bat.vbs" at same folder) that just contains:

@"%0.vbs" %1

the @ means to not output the row on the display (I found it to show garbage text even if you use chcp 1250 as first command)

don’t use double-quotes around %1, it won’t work if your VBScript uses logic like the following (code I was using below was from http://jeffkinzer.blogspot.com/2012/06/vbscript-to-convert-excel-to-csv.html). Tested it and it works fine with spaces in the file and folder names:

  Dim strExcelFileName
  strExcelFileName = WScript.Arguments.Item(0) 'file name to parse

  ' get path where script is running
  strScript = WScript.ScriptFullName
  Dim fso
  Set fso = CreateObject ("Scripting.FileSystemObject") 
  strScriptPath = fso.GetAbsolutePathName(strScript & "\..")
  Set fso = Nothing

  ' If the Input file is NOT qualified with a path, default the current path
  LPosition = InStrRev(strExcelFileName, "\")
  if LPosition = 0 Then 'no folder path
    strExcelFileName = strScriptPath & "\" & strExcelFileName
    strScriptPath = strScriptPath & "\"
  else 'there is a folder path, use it for the output folder path also
    strScriptPath = Mid(strExcelFileName, 1, LPosition)
  End If
  ' msgbox LPosition & " - " & strExcelFileName & " - " & strScriptPath

Gotcha: use parentheses around ternary op conditional expressions

Just came across the following case in C# that puzzled me momentarily, especially since the strings involved were long enough and the expression was broken on multiple lines:

bool flag = true;
string test1 = flag? "xa" : "xb";
string test2 = "x" + (flag? "a" : "b");
string test3 = "x" + flag? "a" : "b";

The test3 case fails since the compiler tries to evaluate the "x" + flag expression at the left side of the operator ?, complaining that it cannot implicitly convert string to bool.

e.g I accidentally produced the following wrong code while I was trying to refactor some third-party code to make it more readable:

string test3 = “some long string here” +

                     (someFlag)?

                     “some other long string”

                     :

                     “some alternative long string”;

whereas the correct was:

string test3 = “some long string here” +

                     ( (someFlag)?

                     “some other long string”

                     :

                     “some alternative long string” );

The correct one needs the whole conditional expression enclosed in parentheses. In fact, the parentheses around (someFlag) can be skipped. In usually prefer them visually, even when I use just a boolean variable instead of a more complex boolean expression, but in case like this one the x + (someFlag)? a : b can be confusing to the reader of the code, not seeing x + (someFlag) will be treated as the conditional instead of someFlag.

Luckily the C# compiler is deterministic enough and not trying to imply meaning from one’s wrong syntax. Had it been some futuristic AI-based interpreter, well, it might have gotten confused there too.

To avoid this confusion in the first place, probably one could have designed a (bool)?x:y operator instead with parentheses being required around the boolean expression, but people coming from other languages (say C++) might then end up writing bool && (bool)?x:y and expect the condition to be bool && (bool) instead of just (bool), so even that syntax would be problematic.

Speaking of other languages, quoting from Wikipedia article on ternary operator:

C++

Unlike in C, the precedence of the ?: operator in C++ is the same as that of the assignment operator (= or OP=), and it can return an lvalue. This means that expressions like q ? a : b = c and (q ? a : b) = c are both legal and are parsed differently, the former being equivalent to q ? a : (b = c).

So confusion even between languages that share a C-style syntax might not be avoided.

Fix: Temporary or Local roaming profile message after Windows login

  Researching how to solve a “You have been logged on with a temporary profile” message on a system set up to use a roaming profile (and after I had first checked/fixed the filesystem for errors which is the classic cause for that when using local profiles) I came across this article:

http://www.grouppolicy.biz/2011/07/how-to-reset-a-roaming-profile-in-windows-7

Near the end of the article they mentioned a registry trick from

https://social.technet.microsoft.com/Forums/windows/en-US/5ec0b949-effa-4e30-ba09-dc948a4c7a8b/windows-7-starting-with-a-temporary-profile?forum=w7itprogeneral

So I tried just the registry trick without even logging into an other admin account (it was an admin account that had the problem, although I know people suggest to avoid roaming for those), that is I renamed the account’s profile key under HKLM\Windows NT\Current Version\ProfileList with an appended .bak extension (instead of backing it up externally and then deleting it) and then did log off and log on again.

To find the correct child key to rename, just check each one there and see which one has the ProfileImagePath value for the profile you’re interested in. If for the login you’re using a Microsoft account instead of a local or ActiveDirectory based one and you’re not sure which name it uses underneath, then you can type the text %userprofile% at Search on the taskbar and press ENTER to see which folder path it opens.

  All was then fine on that machine, but then the rest of the computers that were fine before started complaining that due to some problem with loading the roaming profile they loaded a local copy of it instead.

The fix I devised for that issue was to log into those computers with the problematic account, rename the key for the profile again there (adding the .bak extension), log off and log on again, then rename the key back to normal and log off and log on again. That stopped the complaining (simple log off/log on without that renaming wouldn’t fix it).

Guess what that did was to not load the roaming profile, but keep referencing it while using the local copy instead and the log off after the renaming of the key to correct value again uploaded the correct profile (from the local copy) to the server.

Note that to open regedit and restore that key’s name after logging in the 2nd time (start menu and taskbar’s search wasn’t working anymore) I had to use CTRL+SHIFT+ESC (was on a remote desktop session) and at the task manager select to see more details, then use its File/Run… menu and give regedit as the command to execute.

  Btw, I’ve also seen the Reprofiler tool being mentioned, probably it can copy the roaming profile over a local copy or vice-versa if needed – https://iwrconsultancy.co.uk/reprofiler – https://sourceforge.net/projects/reprofiler/ without having to resort to registry hacks to trick the respective service into copying in the direction one wants.

Fix: Windows 10 laptops with AMD graphics adapter going really slow

Quoting my comment from a discussion thread on the AMD Community website

Actually the drivers back from 2015 that Microsoft serves for AMD laptop graphics adapters seem to make Windows 10 crawl (at everything, not just in graphics stuff). Installing the newest ones from AMD (in this case the beta driver) makes laptops work decently again.

This doesn’t occur only with the legacy adapters like HD 7310, but also with a bit newer supported adapters like AMD Radeon HD 8600M (have one combined with Intel HD on a Dell Inspiron laptop)

Also note that at every Windows 10 feature upgrade, it seems to replace the newer driver with that older one and then you have to replace it with the newer one again (assuming you had the newer installed before, can do it by right clicking Start menu, selecting to see Device Manager, opening display adapters, right clicking the AMD adapter and selecting Update Driver, then selecting to use one on the computer and then select to pick from list and choose the one with the newest release date or the one with bigger version [those two don’t always seem to be "matching"])

This thing makes both Microsoft and AMD look very bad and wonder when they’ll realize it

So if you have a graphics adapter like AMD Radeon HD 7310 grab the Radeon Software Crimson Edition Beta that contains a beta driver from 2016 (don’t get the WHQL one that comes with the older Catalyst Software Suite or the old one coming from Windows Update) from the AMD legacy drivers page:

https://support.amd.com/en-us/download/desktop/legacy?product=legacy3&os=Windows%2010%20-%2064

And if you have a newer graphics adapter like AMD Radeon HD 8600M that is still supported, get the latest AMD driver using https://support.amd.com/en-us/download/auto-detect-tool

Do not use the old back from 2015 driver that Windows Update installs and make sure you replace it with the newer driver again every time manually after you do a Windows 10 feature upgrade (the periodic major update that Microsoft releases). That is till Microsoft decides to fix this issue and install a performing graphics driver (e.g. have AMD get that legacy driver out of beta) and moreover to not replace your graphics driver with an old problematic one that makes your system crawl to its knees.

Καταχρηστικές πρακτικές από Δημοτικές Επιχειρήσεις Ύδρευσης / Αποχέτευσης

Θαυμάστε παρακάτω ένα έγγραφο (από ΔΕΥΑ Αγρινίου, έτους 2015) που δείχνει το αίσχος με πολλές επιχειρήσεις ύδρευσης / αποχέτευσης που καταχρηστικά ορίζουν ως τριτεγγυητή για χρέη του ενοικιαστή προς αυτές τον ιδιοκτήτη ενός ακινήτου. Αναφέρει:

Δηλώνω δε με την παρούσα, ότι είμαι υπεύθυνος εξ’ ολοκλήρου για την εξόφληση των λογαριασμών, σε περίπτωση κατά την οποία, ο μισθωτής μου αφήσει οφειλές.

Αναζητώντας παλιότερα (2015) στο δίκτυο τους κανονισμούς ύδρευσης σε διάφορες πόλεις στον ελλαδικό χώρο, διαπίστωσα πως πρόκειται για διαδεδομένη πρακτική, αν και κάποιες επιχειρήσεις ύδρευσης αποτελούν εξαίρεση μιας και αναζητούν τα χρέη του ενοικιαστή (εφόσον ο λογαριασμός ύδρευσης είναι στο όνομα του) από αυτόν δικαστικά ή μέσω της εφορίας (υπάρχει και νομοθεσία σχετική αν θυμάμαι καλά για την υποχρέωση αναζήτησης χρεών προς Δημοτικές Επιχειρήσεις μέσω της εφορίας) όπως θα περίμενε κανείς από Δημοτικές Επιχειρήσεις.

Αντί γι’αυτό, όσες εταιρείες ύδρευσης έχουν τέτοιους καταχρηστικούς όρους, εκβιάζουν τον ιδιοκτήτη (με πρόφαση την υπογραφή ενός τέτοιου καταχρηστικού εγγράφου αίτησης αρχικής σύνδεσης στο δίκτυο ή ακόμη χειρότερα επικαλούμενες έναν κανονισμό ύδρευσης που αυθαίρετα όρισαν μόνοι τους αντί να ορίζει κάποιον ενιαίο το Δημόσιο για όλες τις εταιρείες αυτές), αρνούμενοι να παρέχουν νερό για τον επόμενο ενοικιαστή αν δεν αποπληρώσει ο ιδιοκτήτης τα χρέη του προηγούμενου ενοικιαστή (μόνο με εντολή εισαγγελέα – έχουν υπάρξει τέτοιες απ’ότι έχω ακούσει – μπορείς να τους υποχρεώσεις να συνδέσουν το νερό άμεσα).

Θυμήθηκα μάλιστα πως κάποτε η ΔΕΗ υποχρεώθηκε τελικά να επιτρέπει συμβόλαιο με τον ενοικιαστή αντί με τον ιδιοκτήτη ενός οικήματος και να αναζητά από τον (νυν ή πρώην) ενοικιαστή τυχόν χρέη που άφησε. Υποθέτω υπάρχουν και σχετικές πρότερες δικαστικές αποφάσεις ή σχετικές υπουργικές αποφάσεις / νομοθετικές ρυθμίσεις από τότε (μου φαίνεται ήταν επί θητείας Βάσως Παπανδρέου στο Υπουργείο Ανάπτυξης). Αυτές προφανώς θα έπρεπε να ισχύουν και για τις Δημοτικές Επιχειρήσεις Ύδρευσης δεδομένου ότι το νερό είναι Κοινωνικό Αγαθό, πέραν του ότι ως Επιχειρήσεις δεν μπορούν να εκμεταλλεύονται τη μονοπωλιακή θέση τους για να εκβιάζουν τους καταναλωτές βάζοντας τους να υπογράφουν συμφωνητικά με καταχρηστικούς όρους.

Μετά απορούμε για τα κλειστά διαμερίσματα ή γιατί ο άλλος προτιμάει να χρονομισθώσει με την Airbnb, αντί να δεσμευτεί με κάποιον ενοικιαστή στον οποίο δε θα μπορεί εύκολα να κάνει έξωση και ο οποίος θα τον φεσώσει ακόμη και με το νερό πέραν από τα ενοίκια και το φόρο που θα πρέπει να πληρώσει γι’αυτά στην εφορία (μέχρι τελεσίδικης δικαστικής απόφασης) κι ας μην τα εισέπραξε.

Υ.Γ. Θα έπρεπε να έχω δημοσιεύσει το παρόν από το 2015, αλλά το είχα αναβάλλει για να μαζέψω και οργανώσω τους ανά την Ελλάδα κανονισμούς ύδρευσης για μετέπειτα καταγγελία στη Γ.Γ. Καταναλωτή. Δυστυχώς δεν πρόλαβα ν’αφιερώσω τον απαραίτητο χρόνο τότε. Με αφορμή συζήτηση για τη δυσκολία εύρεσης ακινήτων προς ενοικίαση και δεδομένου ότι υπάρχουν κλειστά διαμερίσματα λόγω του αυξημένου κόστους διαχείρισης τους αν ενοικιαστούν, στο οποίο συνεισφέρουν αυτές οι καταχρηστικές πρακτικές (αύξηση ρίσκου του ιδιοκτήτη), με τα παρόντα στοιχεία θα προβώ σε κοινοποίηση στη ΓΓ Καταναλωτή άμεσα και πέραν του να σταματήσει αυτές τις καταχρήσεις (το οποίο μπορεί άμεσα), ελπίζω να προωθήσει το θέμα και στο αρμόδιο υπουργείο για νομοθετική ρύθμιση (ενιαίο κανονισμό ύδρευσης).

Ελπίζω να σταματήσουν επιτέλους αυτές οι καταχρηστικές πρακτικές και αποκτήσουμε έναν ενιαίο κανονισμό ύδρευσης που να ορίζει το κράτος (και όπου να μπορούν να κάνουν παραρτήματα οι επιχειρήσεις ύδρευσης αν χρειαστεί, αρκεί να τα υπογράφει ο αρμόδιος υπουργός και να μην αντιβαίνουν στους όρους του ενιαίου κανονισμού ύδρευσης και στο πνεύμα του νομοθέτη με προσθήκη καταχρηστικών όρων).

Twitter #bot or not? Posing as Automation/AI specialist & Python guru

I just came across this strange thing on twitter:

Received a notification that some “Virginia A. Osborn” liked a tweet of mine that was a) in Greek b) totally of a subset of Greek residents interest, highly unlikely it would have meant anything to a person with a foreign name. Then I saw that twitter account started following me immediately after that action.

It smelled bot to me, so I took a look at that account’s profile. She seems to have joined twitter long ago (back in 2010), so I should have probably checked her very first tweets to see what that account was about (wonder if there is an archive of what the profile details were saying back then in case that was a dormant bot).

It seems that account has quite some followers and several people that post AI-related stuff that I follow, plus it didn’t seem to post unrelated stuff:

However, I decided to look her name up on Google. I didn’t find much. So I used Google Image Search to look up her profile photo and bingo, it came up at a stock photo collection, together with other shots of that model posing on the same business-related stock photo theme: https://www.istockphoto.com/search/stack/694124043?assettype=image

So, is this a personna account of some real person or are bots ruling twitter (wonder what the ratio of real persons to bots is currently) and preparing for a future bot-a-gedon? (similar to infected zombie machines that is – wonder if such accounts are already pushing malware links or something).

Of course there’s always the chance that person did a deal with some photographer to pose for him and then used one of those photos in their profile, but I find it highly unlikely.