Wednesday, 18 March 2020

MS SQL Collation Issues

Fixing MS SQL Collation Differences

By Strictly-Software

With the quiet due to the COVID19 virus scare, and all major sporting events being cancelled for the foreseen future, I decided now would be a good time to get my old Automatic Betting BOT back up and working.

It used to run on a dedicated Windows 2003 server and MS SQL 2012 database before being outsourced to a French hosting company, OVH, which doesn't allow HTTP connections to online betting sites, basically making it useless. A decision that wasn't mine in anyway, but basically stopped my BOT from working.

It used to run as a Windows Service using Betfair's Exchange API to automatically create betting systems and then place bets based on systems with a rolling ROI > 5% over the last 30 and 100 days.

I am trying to get the BOT working on a local laptop and therefore it had already been backed up, the files FTP'd down to my laptop and restored in a local MS SQL Express database. 

However it seems that I had some issues when I previously attempted this some time ago as the database now had a different collation, a number of tables were now empty and had been scripted across without indexes and there was a collation difference between many columns and the new DB collation.

Just changing the Databases collation over isn't a simple act on it's own if numerous table column collations are different as well, especially those used in Indexes or Primary Keys as those references need to be removed before the collation can be changed.

A simple fix when it's only a small issue with one column might be to just edit the SQL where the issue arises and use a COLLATE statement so that any WHERE clause or JOIN uses the same collation e.g

SELECT  MemberID, MemberName
WHERE MemberName COLLATE SQL_Latin1_General_CP1_CI_AI = @Name

However when you have a large database and the issue is that half your tables are one collation, the other another, and you need to decide which to move to, and then it becomes more difficult.

My new Database was using the newer SQL_Latin1_General_CP1_CI_AI collation whilst a subset of the tables were still using the older collation from the servers copy of the database Latin1_General_CI_AS.

Therefore finding out which columns and tables were using this collation was the first task and can easily be done with a system view with some SQL like so:

-- find out table name and columns using the collation Latin1_General_CI_AS
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE collation_name = 'Latin1_General_CI_AS'
ORDER BY table_name, column_name

From this I could see that the majority of my tables were using the older collation Latin1_General_CI_AS and that it would be easier to change the database collation to this, then the table collation.

However as there were very few indexes or keys I decided to do the reverse and use the newer collation SQL_Latin1_General_CP1_CI_AI, and change all columns using the LATIN to this new version.

However as I want to show you what to do if you need to change your MS SQL database and columns over to a new collation just imagine I am doing the opposite e.g I am changing my whole system from SQL_Latin1_General_CP1_CI_AI to Latin1_General_CI_AS.

If you did want to change the database collation after it had been created it is not as simple as just opening the databases property window and selecting a new collation OR just running the following query to ALTER the database. Just read the error message I was getting when attempting this.

USE master;

COLLATE Latin1_General_CI_AS ;

Msg 5030, Level 16, State 5, Line 18
The database could not be exclusively locked to perform the operation.

Msg 5072, Level 16, State 1, Line 18
ALTER DATABASE failed. The default collation of database 'MyDatabase' cannot be set to Latin1_General_CI_AS.

Apparently this locking issue is due to the fact that SSMS opens a second connection for Intellisense.

Therefore the correct way to do this, even if you are the only person using the database, as I was, is to put it into single user mode, carry out the ALTER statement, then put it back in multi user mode. #

This query does that.




Once the database has been changed to the right collation you will need to change all the tables with columns using the differing collation. However any columns being used in Indexes or Keys will need to have their references removed first. Luckily for me I didn't have many indexes at this point.

However I still needed to script out any Indexes and Key Constraints I had and save them into an SQL file so that they could easily be re-created afterwards.

I then dropped all the Indexes and Keys that needed to be removed and then ran the following piece of SQL which outputs a load of ALTER TABLE statements.

You may find like I did, that you actually need to add a COLLATE statement into the WHERE clause to get it to work without erroring as the system tables themselves may have a different collation than the one you are wanting to search for.

SELECT 'ALTER TABLE ' + quotename( + '.' + quotename( + 
       ' ALTER COLUMN ' + quotename( + ' ' + type_name(c.system_type_id) 
   WHEN c.max_length = -1 THEN '(max)'
   WHEN type_name(c.system_type_id)  = 'nvarchar' THEN '('+CONVERT(varchar(5),c.max_length/2)+')'
   ELSE '('+CONVERT(varchar(5),c.max_length)+')'   
  END + CASE WHEN c.[precision] = 0 THEN ' COLLATE '+c.collation_name   + ' ' ELSE ' ' END + LTRIM(IIF(c.is_nullable = 1, '', 'NOT ') + 'NULL ' )
FROM  sys.objects o
JOIN  sys.columns c ON o.object_id = c.object_id
JOIN  sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'U' 
  AND c.collation_name <> 'Latin1_General_CP1_CI_AI' -- the collation you want to change from
ORDER BY o.[name]

You should get a load of SQL statements that can be run in a new query window to change all your columns.

It is best to run the previous SQL outputting to a textual window so that the output can easily be copied and pasted.

ALTER TABLE [dbo].[CANCEL_KILLED] ALTER COLUMN [RecordDetails] nvarchar(max) COLLATE Latin1_General_CI_AS

Once you have run all these ALTER statements your whole database should be the collation that you require.

You can then take your copy of the Indexes and Keys that you created earlier, and run them to recreate any missing Indexes and Constraints. You should then be able to remove any quick fix WHERE clauses COLLATE statements that you may have used as a quick fix.

Collation differences are a right pain to resolve but if you do everything in order, and keep saved records of ALTER and CREATE statements that you need along the way it can be something fixed without too much work.

There are some large scripts to automate the process of dropping and re-creating objects if you want to use them however I cannot test to the reliability of these as I chose to do everything bit by bit, checking and researching along the way.

By Strictly-Software

Wednesday, 4 March 2020

Samsung Phones SmartView Application Bug

Fixing Problems With Samsung Phone SmartView Application

By Strictly-Software 

I have a Samsung Galaxy S8 mobile phone and recently I have had issues with the inbuilt SmartView application that allows you to mirror the screen of your phone to nearby devices such as Televisions, Rokus, Chromecasts and other Wifi enabled devices.

You access the app by using the swipe down settings panel at the top of the phone. It's on the second page accessed by swiping left and it's in the top right hand corner. See the below screenshot of my settings panel.

Whilst many applications that let you play videos have a "Play with external device" option that allows you to select the useful app AllCast before the device to then show the video on, some apps don't have a Chromecast beam square or option to play on an external device. 

This is where mirroring your screen is useful as you can just mirror yours phones screen to a TV and anything you do on the phone is then shown on the TV. The downside is that you can't use the phone or other apps whilst mirroring without your TV showing that app. However if you can use AllCast 
you can play a TV show, film or video from your phone on your TV but still use your phone to access other apps or make calls.

Up until last week everything worked fine, I could select SmartView and the apps little loading icon spins before local devices are shown to pick from. In my lounge I can pick from my Samsung 4k TV or the Chromecast device attached to it. This also allows me to beam videos from apps like YouTube that I don't want to mirror my phones screen to, or apps like sporting apps that don't have options to use AllCast to beam with.

In another room I have a Panasonic TV which also has a Roku attached and both could be used by AllCast or my phone to mirror to.The SmartView app used to be able to pick all 4 devices up and I could choose which TV or device to mirror my phone to.

However for some reason as I was watching a football game by mirroring my screen so the app could show the game on a big screen I left my pad. The connection was disconnected but when I came back I tried to use SmartView to re-connect it to my TV, but no devices were shown as available. In fact it didn't even look like it was scanning the room to find any devices to play to. Nothing was spinning in the corner and only the About Us and Contact us options were shown.

I filed numerous bug reports using Samsung's useful Members app and I had a reply that told me to go into System Settings, Applications, Select Show System Apps, choose SmartView then clear its cache. I did this but it didn't fix anything.

However tonight I came across a fix. I had to turn developer options on. You do this by going into Settings then About Phone and in there select Software Information before clicking on the Build Number about 7 times and it will give you the Developer Options.

In there you scroll down to Networking and turn on the option called "Wireless Display Certification"..

I then went into SmartView and it started spinning again, however a new panel appeared at the bottom with numerous WiFi connection options that I just ignored.

I then just scrolled to the bottom of these options and there appeared my selection of devices, in this case my TV and Chromecast.

And on choosing one, once again my phones screen was now mirrored to my TV.

I have no idea why this worked or what stopped SmartView from working in the first place but by doing my own debugging and playing around with the networking options I managed to solve what Samsung Tech Support couldn't. 

Hopefully this fix may help other Samsung phone users who have the same issue as me.

By Strictly-Software

© 2020 Strictly-Software

Friday, 24 January 2020

Making It Hard For Humans and BOTS to use SELECT Inputs

How To Ensure A Human Has To Select From A List Rather Than Just Hitting A Letter, Word or Number

By Strictly-Software

I noticed something the other day when logging into Halifax my bank, when you get to the memorable information form, you have to select 3 letters picked by random from your word

They will have 3 List boxes and they will pick 3 positions in your memorable phrase to select e.g letter 4, 7, 15.

I used to be able to just tab though the list boxes and hit the right letter or number and get it selected but no more.

At first I thought it might be something to prevent BOTS from using the form, and preventing bank fraud etc but when I inspected the SELECT inputs they used I noticed they were doing something else,

They have just put non breaking HTML space entities in front of all the values and options e.g

<option value="&nbsp;a">&nbsp;a</option>

So they have just put a space character in front of the value and in the viewable list. However they haven't used a space as in a " " gap, they have used an encoded space as quick fingers would easily be able to select the box and hit the space bar and the letter to get to their value.

However it's not so easy to write out a whole &nbsp; character and the letter to select it without using your mouse.

I don't know how this would effect BOTS however as someone who is designing a site for phising attacks would examine their HTML code and could easily write something that passed 3 letters to the server on submit with a non breaking space in front of them e.g &nbsp;a.

However it certainly makes sure that a human actually pays attention and USES the select box with a mouse rather than tabbing through.

If anyone can see how this increases security on Bank forms let me know as seems to not prevent BOTS - unless automated and those who don't know where they are from attacking a page. It might mean the rewriting of a generic intrusion tool to handle list boxes like this but that would be easy.

It just seems to make it harder for legitimate users from using their fingers and not the mouse from what I can see. However it does make sure you are more careful when entering the values. So maybe that is all they wanted to do.

If anyone can see any other reasons why putting a non breaking space character in the list box options is a good idea let me know please.

By Strictly-Software

© 2020 Strictly-Software