SQL To Find The Latest Modified Database Objects

SQL To Find The Latest Modified Database Objects

By Strictly-Software

Lots of times I want to quickly see which database objects I have modified lately without having to open up specialist programs such as AdeptSQL or Redgate etc.

By using the System Views you can easily find the objects you have recently created or modified.

The sys.objects view is what we use here and we can filter the types of object very easily with the [type] column.

The (main) values for this are:

P = Stored Procedure 
U = User Table (includes non clustered indexes added to it) 
D = Default Value Constraint 
FN = Scalar User Defined Function 
TF = Table User Defined Function 
PK = Primary Key 
UQ = Unique Constraint 
SN = Synonym
V   = View

If you really wanted to, you could search the system tables, default constraints, and other objects such as...

D   = Default Constraint
F   = Foreign Key Constraint
FS = CLR Scalar Function
PC = CLR Stored Procedure
IF  = SQL Inline Table Valued Function
IT  = Internal Table
S    = System Table
SQ = Service Queue
X   = Extended Stored Procedure


This example however looks for the latest modified User Defined Functions (Scalar and Table), and Stored Procedures.

SELECT name, create_date, modify_date, [type]
FROM sys.objects
WHERE [type] IN('P' , 'FN', 'TF')
ORDER BY modify_date DESC

This example looks for the most recent created stored procedures that start with the name usp_net_save

SELECT name, create_date
FROM sys.objects
WHERE [type] = 'P' 
 AND name like 'usp_asp_save%'
ORDER BY create_date DESC

This is a very quick and easy way to find the code in an SQL database that you have either modified or updated.

By Strictly-Software

© 2016 Strictly-Software

Quick HTACCESS Rules and VBS / ASP Functions for Banning Hackers

Quick HTACCESS Rules and VBS / ASP Functions for Banning Hackers

By Strictly-Software

Having to work on some old sites sometimes means that the security is often pretty lax and even if the system is locked down to prevent SQL Injection by having proper permissions, e.g not allowing CUD (Create, Update, Delete) statements to run from the front end, there is still the possibility of XSS hacks on an old ASP Classic site.

Running an exploit scanning tool like the OWASP ZAP tool is a good idea to find possible holes but even then I have found that it doesn't find all possible exploits. There are hackers looking to harm you and then there are the "ethical hackers" who will still try and probe your site to "warn you", and the "unethical" sort who try to blackmail customers by putting your URLS up on special websites that claim the site is a security minefield.

Even if your browser protects you from most cases, e.g trying to find a recent hack in Chrome was a nightmare due to it automatically protecting me by changing the HTML source, these reputation attacks will hurt your company and customers. Therefore even if you are not given the time or cannot fix every hole that could exist in your system, you should do as much as possible to prevent them from being found in the first place.

Having a proper WAF (Web Application Firewall), is always recommended but even if you don't you can make use of your .htaccess file to block a lot of probing plus you can always use code to validate requests, such as form submissions on contact forms or other pages that accept user content.

Having a CAPTCHA is always a good idea, and if not use a BOT TRAP if at all possible, however this won't stop a human with the spare time to examine your source code and work out what is going on to get around it either manually or by writing a custom BOT.

HTACCESS Rules

There are many .HTACCESS rules you can use to block attacks but I have found that some are overly obtrusive and will block legitimate requests.

For example if you want a good list of possible .htaccess rules to prevent probes from PHP, JavaScript, MySQL and WordPress then downloading the free plugin WP Security is a good way to see the sort of rules that can be applied to an .htaccess file.

Just turn on all the firewall, image hot linking and file protection rules you want and then go and view the .htaccess file in your root folder.

These rules are quite comprehensive and most are generic enough to be copied and used on Microsoft platforms that support .htaccess files if you want. Obviously a PHP hack isn't going to work on an IIS server but if you want to still catch people trying these hacks then having a page that logs and bans them if possible is an idea.

I won't show you all the rules you can take from other plugins but I will show you some core rules that can cut down your "Bad Traffic" by a huge percentage.

I have found on my own sites that banning any Internet Explorer version under 7 is always a good idea as many of these hacker tools that script kiddies use, still have a default user-agent of IE 5-6, and for some reason these people don't bother changing the user-agent.

Therefore just examining your IIS log files for any user with IE 5, 5.5 or 6 user-agent is a good indication that they are up to no good. You can read about this and another way to reduce bandwidth on another article of mine here.

RewriteRule %{HTTP_USER_AGENT} (MSIE\s6\.0|MSIE\s5\.0|MSIE\s5\.5) [NC]
RewriteRule .* http://127.0.0.1 [L,R=302]

This rule sends any user-agent with IE 5, 5.5 or 6 back to the localhost on the users machine with a 302 rewrite rule. 

You could just use a [F] Forbidden (403) rule if you want but at least this way you are going to piss the offenders off a bit more by sending them in circles.

Here are some more rules I use regularly which sends the user to a special hack.asp page where I can log their details and bounce them to a honeypot or a circular link maze of my choice.

As you can see the rules cover some common SQL Injection attacks that utilize the system tables, anything trying to be executed (EXEC), plus <Script> tags using standard and URL encoded brackets.

This is because a lot of "ethical hackers" or probers will try simple <script>alert("Hacked")<script> tests on any search form they can find on your site.

If the page you get posted to pops up an alert box then you are vulnerable to Cross Site Scripting attacks.

Other methods they commonly use are HTML tags that allow for onload functions to be run as well as "break out" code that tries to close an HTML element, then output it's own HTML or JavaScript code.

# SQL INJECTION and XSS FINGERPRINTING
RewriteRule ^/.*?\.asp\?(.*?DECLARE[^a-z]+\@\w+[^a-z]+N?VARCHAR\((?:\d{1,4}|max)\).*)$ /jobboard/error-pages/hack\.asp\?$1 [NC,L,U]
RewriteRule ^/.*?\.asp\?(.*?sys.?(?:objects|columns|tables).*)$ /jobboard/error-pages/hack\.asp\?$1 [NC,L,U]
RewriteRule ^/.*?\.asp\?(.*?;EXEC\(\@\w+\);?.*)$ /jobboard/error-pages/hack\.asp\?$1 [NC,L,U]
RewriteRule ^/.*?\.asp\?(.*?(%3C|<)/?script(%3E|>).*)$ /jobboard/error-pages/hack\.asp\?$1    [NC,L,U]
RewriteRule ^/.*?\.asp\?(.*?((%2[27])%3E(%20)*%3C|['"]>\s*<).*)$ /jobboard/error-pages/hack\.asp\?$1    [NC,L,U]
RewriteRule ^/.*?\.asp\?(.*?(<svg|alert\(|eval\(|onload).*)$ /jobboard/error-pages/hack\.asp\?$1    [NC,L,U]

# Block blank or very short user-agents. If they cannot be bothered to tell me who they are or provide jibberish then they are not welcome!                                                       
RewriteCond %{HTTP_USER_AGENT} ^(?:-?|[a-z1-9\-\_]{1,10})$ [NC]
RewriteRule .* - [F,L]

ASP / VBScript Function

As well as having a good set of .htaccess rules to prevent hacks by QueryString you can always use a function to parse any content through to look for hacks. Of course it is possible to use .htaccess rules to filter out HTTP POST and GET Requests but you may want to prevent too many regular expressions running on every POST with your .HTACCESS file by just passing publicly accessible forms with your function.

A very basic example is below.

Function TestXSS(strVal)
 
 Dim bRet : bRet = False

 '* remove encoded < > etc, use your own URL Decode function, I use a SERVER SIDE JavaScript with their decodeURIComponent function to do this in ASP Classic
 strVal = URLDecode(Trim(strVal))

 '* URL Encoded stuff like %3Cscript%3Ealert(1)%3C%2fscript%3E will get trapped by ISAPI
 '* RegTest is just a generic function I have which does a regular expression test against a value and pattern to see if it matches like C# RegEx.IsMatch
 If RegTest(strVal, "(?:<\/?script(\s|>|)|\W+eval\(|\W+(?:src|onload)=|document\.)") Then
  bRet = True
 Else    
  If RegTest(strVal, "(';|"";|'>|"">|alert\(|document\.cookie|\.frame|\.top\.location|(?:document|location)\.src|on\w+\()") Then
   bRet = True
  Else
   bRet = False 
  End If
 End If

 TestXSS = bRet
End Function

Obviously you can extend this function as much as you want to check for other words or pieces of code that shouldn't be passed around in forms or querystrings. It is just a base for you to start with.

Conclusion

Remember security is best approached in a multi layered way without your system relying on one form of defence alone.

WAFS, HTACCESS rules, code, CAPTCHAS, BOT Traps, special "not to be visited" links in your Robots.txt file that then send visitors to that page off to honeypots for breaking the rules, rude word lists, proper file permissions and HTTP parameter checks are all various ways to protect your site.

However many of these are sticking plasters which are used to protect your code in case it has not been 100% written to sanitise ALL user input, escape content outputted to the page, or has incorrect database security permissions.

Having a properly written and secured system is always the best solution however new exploits are always coming out so having as much protection as possible is not only good for security but it can save on bandwidth costs as well!

© 2016 By Strictly-Software.com

IE Bug with non Protocol Specific URLS

IE Bug with non Protocol Specific URLS

By Strictly-Software

I have recently come across a problem that seems to only affect IE and non protocol specific URLS.

These are becoming more and more common as they prevent warnings about insecure content and ensure that when you click on a link you go to the same protocol as the page you are on.

This can obviously be an issue if the site you are on is on an SSL but the site you want to go to doesn't have an HTTPS domain or vice-versa. However most big sites have both domains and will handle the transfer by redirecting the user and the posted data from one domain to another.

An example would be a PayPal button that's form posts to

action="//www.paypal.com/"

In the old days if you had a page on an SSL e.g https://www.mysite.com and had a link to an image or script from a non secure domain e.g http://www.somethirdpartysite.com/images/myimg.jpg you would get pop ups or warning messages in the browser about "non secure content" and would have to confirm that you wanted to load it.

Nowadays whilst you don't see these popups in modern browsers if you check the console (F12 in most browsers) you will still see JavaScript or Network errors if you are trying to load content cross domain and cross protocol.

S2 Membership Plugin

I came across this problem when a customer who was trying to join one of my WordPress subscription sites (that uses the great free S2 Membership Plugin), complained to say that when he clicked a payment button that I have on many pages, was being taken to PayPal's homepage rather than the standard payment page that details the type of purchase, price and options for payment.

It was working for him in Chrome and FireFox but not IE.

I tested this on IE 11 Win7 and Win8 myself and found that this was indeed true.

After hunting through the network steps in the developer toolbar section (F12) and comparing it to Chrome I found that the problem seemed to be IE doing a 301 redirect from PayPals HTTP domain to their HTTPS one. 

After analysing the response and request headers I suspect it is something to do with the non UTF-8 response that PayPal was returning to IE for some reason, probably because Internet Explorer wasn't requesting it as such for some reason.

Debugging The Problem

For the techies. This is a breakdown of the problem with network steps from both Chrome and IE and relevant headers etc.

First the Paypal button code which is encrypted by S2Member on the page. You get given the button content as standard square bracket short codes which get converted into HTML on output. Looking at the source on the page in both browsers on one button I could see the following.

1. Even though the button outputs the form action as https://www.paypal.com it seems that one of my plugins OR WordPress itself, although I suspect a caching plugin obviously, is changing my links (I haven't been able to narrow it down - or WordPress) is removing any protocols conflicts by using non protocol specific URLS.

So as my site doesn't have an SSL any HREF, SRC or ACTION that points to an HTTPS URL was being replaced with // e.g https://www.paypal.com on my page http://www.mysite.com/join was becoming //www.paypal.com in the source and generated source.

2. Examining the HTML of one of the buttons you can see this in any browser. I have cut short the encrypted button code as it's pointless outputting it all.

<form action="//www.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_s-xclick">
<input name="encrypted" type="hidden" value="-----BEGIN PKCS7-----MIILQQYJKoZIhvcNAQcEoIILMjCCCy4CAQExgg..."

3. Outputting a test HTML page on my local computer and running it in IE 11 WORKED. This is was probably because I explicitly set the URL to https://www.paypal.com so no redirects were needed.

4. Therefore logically the problem was due to the lack of an HTTPS in the URL.

5. Comparing the network jumps.

1. Chrome

Name - Method - Status    - Type                     - Initiator
webscr - POST - 307      - x-www-form-urlencoded    - Other
webscr - POST - 200 - document             - https://www.paypal.com/cgi-bin/webscr

2. IE

URL         - Protocol  - Method - Result - Type       - Initiator
/cgi-bin/webscr         - HTTP      - POST   - 301       -           - click
/cgi-bin/webscr         - HTTPS    - POST   - 302       - text/html  - click
https://www.paypal.com/home - HTTPS   - POST   - 200       - text/html  - click

Although the titles are slightly different you can see they just are different words for the same thing e.g Status in Chrome or Result in IE both relate to the HTTP Status code the response returned.

As you can see Chrome also had to do a 307 (HTTP 1.1 successor to the 302 temporary redirect) from HTTP to HTTPS however it ended up on the correct page. Whereas in IE when I first clicked on the button it took me to the payment page in HTTP but then did a 301 (permanent) redirect to it in HTTPS and then a 302 (temporary) redirect to their home page.

If you want to know more about these 3 redirect status codes this is a good page to read.

The question was why couldn't IE take me to the correct payment page?

Well when I looked at the actual POST data that was being passed along to PayPal from IE on the first network hop I could see the following problem.

cmd=_s-xclick&encrypted=-----BEGIN㲠----MIILQQYJKoZIhvcNAQcEoIILMjCCCy4CAQExgg...

Notice the Chinese character after the BEGIN where it should say PKCS7?

In Chrome however this data was exactly the same as the form e.g

cmd:_s-xclick
encrypted:-----BEGIN PKCS7-----MIILQQYJKoZIhvcNAQcEoIILMjCCCy4CAQExgg...

Therefore it looked like for some reason the posted data was being misinterpreted by IE whereas in Chrome it was not. Therefore I needed to check what character sets and response was being sent and returned.

Examining Request and Response Headers

When looking at the HTTP Request headers on the first POST to PayPal in IE I could see that the Accept-Language header was only asking for en-GB e.g a basic ASCII character set. Also there was quite a lack of request headers compared to IE. I have just copied the relevant ones that can be compared between browsers.

IE Request Headers

Key         Value
Content-Type: application/x-www-form-urlencoded
Accept-Language: en-GB
Accept-Encoding: gzip, deflate
Referer: http://www.mysite.com/join-now/
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko
Request: POST /cgi-bin/webscr HTTP/1.1
Accept: text/html, application/xhtml+xml, */*
Host:         www.paypal.com

Chrome Request Headers

Key                 Value
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
Accept-Encoding:gzip, deflate
Accept-Language:en-GB,en-US;q=0.8,en;q=0.6
Cache-Control: max-age=0
Connection: keep-alive
Content-Length:4080
Content-Type: application/x-www-form-urlencoded


And the responses for the Content-Type header which I think is key.

IE

Content-Type: text/html

Chrome

Content-Type: text/html; charset=UTF-8


So whilst Chrome is saying it will accept more language sets and gets back a charset of UTF-8 IE is only saying it will accept only en-GB and gets back just text/html.

I even tried seeing if I could add UTF-8 in as a language to accept in IE but there was no option to, so I tried adding Chinese which obviously use extended character sets and was the problematic character.
 However this made no difference even though the Accept-Language header was now:

Accept-Language: en-GB,zh-Hans;q=0.9,zh-CN;q=0.8,zh-SG;q=0.7,zh-Hant;q=0.6,zh-HK;q=0.4,zh-MO;q=0.3,zh-TW;q=0.2,zh;q=0.1

Conclusion

Therefore I came to the conclusion that I could not force IE to change it's behaviour and I doubt any phone calls to IE HQ or even PayPal would solve the issue. Therefore to allow IE users to be able to still pay on my site I needed a workaround.

1. I added to my technical test page which I make all users check before paying a test for IE and a warning about possible problems. This went alongside tests to ensure JavaScript and Cookies were enabled as both are needed for any modern JavaScript site.

2. I added in some JavaScript code in my footer that run on DOM load that looped through all FORM elements and checked the ACTION attribute. Even though when examining the results in the console they showed http://www.paypal.com rather than what I saw in the source //www.paypal.com I added some code in to ensure they always said HTTPS.

The function if you are interested is below and it seems to have fixed the problem in IE. If I view the generated source now I can see all form actions have HTTPS protocols.

// on DOM load loop through all FORM elements on the page
jQuery(document).ready(function () {	
	// get all form elements
	var o,,e=document.getElementsByTagName("FORM");
	for(var i=0,l=e.length;i<l;i++)
	{
		// get the action attribute
		o = e[i].action;

		// if current action is blank then skip
		if(o && o!="")
		{
			// if the start of the action is http (as non protocol specifc domains show up as http)
			// then replace the http with https
			if( /^http:\/\/www.paypal.com/.test(o) )
			{
				e[i].action = o.replace("http:","https:");
			}		
		
		}
	}	
});

So whilst this is a just a workaround for the IE bug it does solve the issue until Internet Explorer sorts itself out. Why they have this problem I have no idea.

I am outputting all my content as a UTF-8 charset and Chrome is obviously handling it correctly (along with Firefox and Safari).

So I can only presume it's an IE bug which isn't helped by an unknown (as yet) plugin (or WordPress) changing cross protocol URLs to the now standard //www.mysite.com format.

Therefore if you come across similar problems with redirects taking you to the wrong place, check your headers, compare browsers and if you spot something strange going on, try a JavaScript workaround to modify the DOM on page load.


© 2015 Strictly-Software

What is the point of client side security

Is hacking the DOM really hacking?

By Strictly-Software

The nature of the modern web browser is that it's a client side tool.

Web pages that are stored on web-servers when viewed in Chrome or FireFox are downloaded file by file (CSS, JavaScript, HTML, Images etc), and stored temporarily on your computer whilst your browser application puts them together so you can view the webpage.

This is where your "browser cache" comes from. It is good to have commonly downloaded files such as the jQuery script or common images from frequently visited pages in your cache but when this folder gets too big it can become slow to traverse and load from. This is why a regular clean out is recommended by a lot of computer performance tools.

So because of this putting any kind of security on the client side is pointless as anyone with a small working knowledge of Internet technology can bypass it. I don't want to link to a certain site in particular but it appeared as a google advert on my site the other day claiming to protect your whole website from theft, including your HTML source code.

However if you have a spare 30 minutes on your hands, have Firebug installed (or any modern browser that allows you to visit and edit the DOM) and did a search for "code to protect HTML" you would be able to bypass the majority of the sites wonderful security claims with ease.

Examples of such attempts to use client side code to protect code or content include:

1. Trying to protect the HTML source code from being viewed or stolen. 

This will include the original right mouse click event blocker.

This was used in the old days in the vain hope that people didn't realise that they could just go to Tools > View Source instead of using the context menu which is opened with a right click on your mouse.

The other option was just to save the whole web page from the File menu. 

However you can now just view the whole generated source with most developer tools e.g Firebug - or hitting F12 in Chrome.

Some sites will also generate their whole HTML source code with Javascript code in the first place. 
Not only is this really really bad for SEO but it is easily bypassed.

A lot of these tools pack, encode and obfuscate it on the way. The code is then run through a function to evaluate it and write it to the DOM. 

It's such a shame that this can all be viewed without much effort once the page loads in the DOM. Just open your browsers Developer Toolbar and view the Generated Source and hey presto the outputted HTML is there.

Plus there are many tools that let you run your scripts on any page e.g someone at work the other day didn't like the way news sites like the BBC always showed large monetary numbers as £10BN and added a regular expression into one of these tools to automatically change all occurrences to £10,000,000,000 as he thought the number looked bigger and more correct.  Stupid example I know but it shows that with tools like Fiddler etc that you can control the browser output.

2. Using special classes to prevent users from selecting content. 

This is commonly used on music lyric sites to prevent people copying and pasting the lyrics straight off the page by selecting the content and using the copy button.

Shame that if you can modify the DOM on the fly you can just find the class in question with the inspect tool, blank it out and negate it's affect.

3. Multimedia sites that show content from TV shows that will remain unnamed but only allow users from the USA to view them. 

Using a proxy server sometimes works but for those flash loaded videos that don't play through a proxy you can use YSlow to find the base URI that the movie is loaded from and just load that up directly.

To be honest I think these companies have got wise to the fact that people will try this as they now insert location specific adverts into the movies which they never used to do. However it's still better than moving to the states!

4. Sites that pack and obfuscate their Javascript in the hope of preventing users from stealing their code. 

Obviously minification is good practise for reducing file size but if you want to unpack some JavaScript then you have a couple of options and there maybe some valid reasons other than just wanting to see the code being run e.g preventing XSS attacks.

Option 1 is to use my script unpacker form which lets you paste the packed code into a textarea, hit a button and then view the unpacked version in another textarea for you to copy out and use. It will also decode any encoded characters as well as well as formatting the code and handling code that has been packed multiple times.

If you don't want to use my wonderful form and I have no idea why you wouldn't then Firefox comes to the rescue again. Copy the packed code, open the Javascript error console and paste the code into the input box at the top with the following added to the start of it:

//add to the beginning eval=alert;
eval=alert;eval(function(p,a,c,k,e,r){e=String;if(!''.replace(/^/,String)){while(c--)r[c]=k[c]||c;k=[function(e){return r[e]}];e=function(){return'\\w+'};c=1};while(c--)if(k[c])p=p.replace(new RegExp('\\b'+e(c)+'\\b','g'),k[c]);return p}('3(0,1){4(0===1){2("5 6")}7{2("8 9")}',10,10,'myvar1|myvar2|alert|function|if|well|done|else|not|bad'.split('|'),0,{}))

// unpacked returns
function(myvar1,myvar2){if(myvar1===myvar2){alert("well done")}else{alert("not bad")}

Then hit evaluate and the unpacked code will open in an alert box which you can then copy from.

What the code is doing is changing the meaning of the function eval to alert so that when the packed code runs within its eval statement instead of executing the evaluated code it will show it in the alert message box.

There are many more techniques which I won't go in to but the question then is why do people do it?

Well the main reason is that people spend a lot of time creating websites and they don't want some clever script kiddy or professional site ripper to come along steal their content and use it without permission.

People will also include whole sites nowadays within frames on their own sites or just rip the whole thing CSS, images, scripts and everything else with a click of a button. There are too many available tools to count and a lot of phishing sites will copy a banks layout but then change the functionality so that it records your account login details.

I have personally seen 2 sites now that I have either worked on or know the person who did the work appear up on the net under a different URL with the same design, images, JS code, all the same apart from the wording was in Chinese .

The problem is that every modern browser now has a developer tool set like Firebug, Chrome or Internet Explorers developer toolbar. For older browsers there are Operas dragonfly and even Firebug-lite which replicates Firebug functionality for those of you wanting to use it on older browsers like IE 6.

Therefore with all these built in tools to override client side security techniques it seems pretty pointless trying to put any sort of security into your site on the client side.

Even if you didn't want to be malicious and steal or inject anything you can still modify the DOM, run your own Javascript, change the CSS and remove x y and z.

All security measures related to user input should be handled on the server to prevent SQL injection and XSS hacks but that's not to say that duplicating validation checks on the client isn't a good idea.

For one thing it saves time if you can inform a user that they have inputted something incorrectly before the page is submitted.

No one likes to fill in a long form submit it and wait whilst the slow network connection and bogged down server takes too long to respond only to show another page that says one of the following:

• That user name is already in use please choose another one.
• Your email confirmation does not match.
• Your password is too short.
• You did not complete blah or blah.

Things like this should be done client side if possible, using Ajax for checks that need database look ups such as user name availability tests. Using JavaScript to test whether the user has JavaScript enabled is a good technique for deciding whether to rely purely on server side validation or to load in functions that allow for client side validation if possible.

However client side code that is purely there to prevent content from being accessed without consent seems pointless in the age of any modern browser.

Obviously there is a large percentage of web users out there that wouldn't know the first thing to do when it comes to bypassing client side security code and the blocking of the right click context menu would seem like black magic to them.

Unfortunately for the people who are still wanting to protect their client side code the people that do want to steal the content will have the skills to bypass all your client side cleverness.

It may impress your boss and seem worth the $50 for about 10 minutes until someone shows you how you can add your own Javascript to a page to override any functions already there for blocking events and checking for iframe positioning.

My only question would be is it really hacking to modify the DOM to access or bypass certain features meant to keep the content on that page?

I don't know what other people think about this but I would say no its not.

The HTML, images, JavaScript and CSS are ALL on my computer at the point of me viewing them on whatever browser I am using. Therefore unless I am trying to change or inject anything into the web or database server to affect future site visitors or trying to bypass challenge responses then I am not really hacking the site just modifying the DOM.

I'd be interested to know what others think about that question?

By Strictly-Software

© 2015 Strictly-Software

Web Developing - My 10 Golden Rules For Developers

Ten Golden Rules for any Web Developer

When you have developed code for a living for the best part of 18 years you soon come up with a list of rules that help keep you sane and prepare you for the next disastrous project coming your way.

As Bismark said - "The wise man learns from the mistakes of others."

So, don't learn from your own mistakes, try and get it right first time.

Here are my own little rules to help you from learning from your own mistakes.

1. Always add comments.

The more comments the better. There is nothing worse than coming back to a bit of complicated code you or someone else wrote years ago and have no idea why they wrote it that way.

Comments can always be stripped before roll out but on a development system they are a life saver especially if you have had to do something out of the ordinary because of some uncommon situation.

2. Format your code.

Proper indentation and casing is most definitely above cleanliness when compared to Godliness.

Not only does it make the code easier to read but it looks better and prevents developers with mild OCD from stressing out and having to spend too much time re-formatting before they can even help you debug that mess you call work.

3. Don't re-develop the wheel.

Having said that don't put up with someone else's flat tyres.

People use libraries because it saves them time but every library has an author and no-one is infallible. The benefits to using your own code is that you know how every line works and can fix bugs immediately. I have not yet come across a 3rd party library that didn't have at least one major flaw and there is a reason new libraries constantly appear in the first place.

Not only will you learn more by writing your own code but you won't have to rely on someone else to fix it all when it goes wrong.

4. Cherish the anally retentive customer.

Whilst anally retentive customers may seem like a pain in the behind in reality you should cherish the fact that they actually know what they want (see point 5).

A customer that knows exactly what he wants up front and is prepared to sign off a spec and keep to it is a rare occurrence and you should be prepared to put up with their constant emails and calls during development.

You should do this because you know that once you have delivered everything they want - even if it looks like a bag of shite and acts like a bag of shite. If it was a bag of shite that they really really wanted in the first place then it will be your bag of shite that makes them happy and brings home the bacon.

A happy customer is one that doesn't ring you constantly months after the live date asking for little changes and wondering if they just have X, Y and Z all for free because they didn't think ahead earlier.

5. Most customers know Jack

The majority of customers will know next to nothing about the system they want and expect you to know how their business is run.

Getting a properly defined spec out of some customers is harder than milking a male cow.

Customers will give you definites and then complain about the lack of flexibility.

They will expect gold plating but only want to pay for metallic.

They will expect you to see into the future and know their business requirements and have them programmed before they do.

You should be prepared for this by building in ultimate flexibility at the earliest opportunity so that when they eventually change their minds you don't have to re-develop their system from the ground up.

6. Ignore No and plan for Yes

When you ask your boss if the system you are working on needs to do X and he says categorically NO. Be prepared for him to change his mind a month or two down the line when the next sale depends on that feature.

Sales people don't care how long it took you to develop something or how hard it was to develop it. All they care about is getting their next commission.

Be prepared to spend months developing features that are used purely for selling a product rather than for their usefulness. Take that opportunity to develop said feature in a new language or use the opportunity to learn a new API and incorporate it into your code. Even if the feature isn't used at least you have learnt a new skill for your CV.

7. Automate, automate, automate.

There is nothing worse as a developer spending time doing all the monotonous tasks like building Class structures, input forms and CRUD stored procs when you could be working on a cool widget, stealthy scraper or funky functionality that actually engages your mind.

For this reason you should automate the CRUD so you can spend your time on the interesting work. There are many tools available to automate code outlines based on database structures and if you always code to a template you should be able to rattle off the basics quickly so that your time is better spent on the interesting aspects of a project.

8. A Metro with a Ferrari engine still looks like a Metro. A Ferrari with a Metro engine still pulls the birds.

Most end users, customers and bosses don't care about the cleverness of your code or how many lines you have had to write to make that balloon go pop. All they care about is how the site looks.

Front end's matter because that's the part the user sees, whereas back-ends are important because they make it all work. However good the database, business logic and code is under the covers if the front end looks like a four year old's first drawing then no-one will care.

In the same way as a sales person takes all the money from your hard work. Your high performing, code wise back end is just the engine that props up the front end that everyone looks at and goes "ooh isn't that nice". OR more importantly makes them decide that your site is rubbish purely because it looks bad.

Remember you could have the best code in the world but if it has a crap design people won't appreciate it.

9. Be prepared to be unappreciated and under paid.

Sales people and bosses will milk your talent to enrich themselves whilst giving you platitudes and praise but keeping the hard cash for themselves.

Making good money from web development is very hard unless you come up with the newest idea that Google might purchase down the road or work for big well paying company.

It's very easy to steal code from the web and with open source it's increasingly hard to make good money when people are throwing their code around for free in the hope that someone likes it enough to pay them for an upgrade or support.

10. Learn to code for fun.

If you don't enjoy coding then you won't be prepared to put the spare time in to make your own projects on the side. No-one ever got rich working for someone else but being able to give up your job to work on your code is impossible unless you have money to back your work. Being able to code for pleasure makes working on your own projects in your spare time a whole lot easier.

Those are my top 10 tips for helping you get through life as a web developer. If you have your own tips please respond using the comment section.

Turning off Windows 10 Privacy Features

Turning off Windows 10 Privacy Features

By Strictly-Software 

If you are like me and on Windows 8.1 then you will probably be constantly bombarded by messages from Microsoft about how you can obtain a free upgrade to Windows 10.

However before you upgrade you should beware of all the security and privacy concerns people have about this operation system.

With a little research it is well known that Microsoft have a close relationship with the US security services such as the CIA / NSA.

They have even bought up businesses such as Skype to prevent having to hack in back doors when they can instead have a wide open front door for all your personnal traffic, calls and texts.

As the video states.

This is a quick guide to fixing privacy concerns in Microsoft's Windows 10 operating system. 

The default settings and applications with Windows 10 have numerous security flaws, privacy problems, keystroke monitors installed and turned on by default.

This guide will help you fix the problems in a simple way.

Be sure to read this article as it will explain all the security holes in detail if you want more information on what Microsoft are monitoring and why.

However this quick overview video should help you on the way.





By Strictly-Software

© 2015 Strictly-Software

DEBUG Stored Procedures Using PRINT or RAISERROR in TSQL

DEBUG Stored Procedures Using PRINT or RAISERROR in TSQL

By Strictly Software 

Usually when I am having to write stored procedures with many sections within a transaction I set a @DEBUG BIT variable at the top of the procedure to help with debug.

Then I add checks to see if it is enabled and at various points in the procedure, usually at the start and end plus before and after each block of code I would output some debug telling me what is going on.

This really helps me when I have problems with the procedure and need to know where any bug is happening.

An example of a basic stored procedure with transactions so I can rollback the code if something goes wrong, plus the use of a TRY CATCH so that I can log error details to a table is shown below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_net_clean_up]
	@SitePK int
AS
BEGIN
	
	SET NOCOUNT ON;

	DECLARE @ROWS INT,			
		@DEBUG BIT

	SELECT @DEBUG = 1

	IF @DEBUG = 1
	  PRINT 'IN usp_net_clean_up - SitePK: ' + CAST(@SitePK as varchar)

	BEGIN TRAN

	BEGIN TRY

        UPDATE	NIGHTLY_JOBS
	SET	Pending = 0
	WHERE	SitePK = @SitePK

	-- capture data errors
	SELECT @ROWS = @@ROWCOUNT

	IF @ROWS = 0 OR @ERROR != 0
	  BEGIN
		IF @DEBUG = 1
		  PRINT 'No rows updated'

		GOTO HANDLE_ERROR
	  END

	UPDATE	LOCK_FILES
	SET	Locked = 0
	WHERE	Locked = 1
		AND SitePK = @SitePK
		AND DATEDIFF(Day,Stamp,GETDATE())=0

	SELECT @ROWS = @@ROWCOUNT

	IF @ROWS = 0 OR @ERROR != 0
	  BEGIN
		IF @DEBUG = 1
		  PRINT 'No rows updated'

		GOTO HANDLE_ERROR
	  END

	END TRY
	BEGIN CATCH
		
		IF @DEBUG = 1
		  BEGIN
			PRINT 'IN CATCH ERROR'
			PRINT ERROR_MESSAGE()
			PRINT ERROR_LINE()
		  END

		 -- all ERROR functions will be available inside this proc
		  EXEC dbo.usp_sql_log_error @SitePK

		  -- rollback after anything you want to do such as logging the error 
		  -- to a table as that will get rolled back as well if you don't!
		  IF @@TRANCOUNT > 0
		    ROLLBACK TRAN

		  GOTO HANDLE_ERROR
	END CATCH

	IF @DEBUG = 1
	  PRINT 'End of proc - no errors'

	IF @@TRANCOUNT > 0
	  COMMIT TRAN

	GOTO EXIT_PROC


	HANDLE_ERROR:
	  IF @DEBUG = 1
	    PRINT 'IN HANDLE ERROR'

	  RETURN 1 -- I use 1 for success despite SQL recommendations!

	EXIT_PROC:
	  IF @DEBUG = 1
	    PRINT 'IN EXIT_PROC'

	  RETURN 0 -- failure
END

However another way to output debug messages is with the RAISERROR function and the use of placeholders for values, a bit like the sprint function in PHP.

To be honest I only used the function previously to raise custom errors but you easily use it for debugging as well.

This is an example of a super fast way to insert 100,000 rows into a table (using the TEMPDB), and using RAISERROR to output debug messages related to the time it takes plus some examples of the placeholder types which are listed at the bottom of the page.

SET NOCOUNT ON
SET DATEFORMAT YMD

DECLARE @StartStamp VARCHAR(20),
	@StopStamp VARCHAR(20),
	@ROWS INT

SELECT @StartStamp = CONVERT(varchar, GETDATE(),13)

RAISERROR('Start insert at %s',0,1,@StartStamp) WITH NOWAIT;

-- ensure our table doesn't already exist
IF OBJECT_ID('tempdb.dbo.random_data','U') IS NOT NULL
  DROP TABLE tempdb.dbo.random_data;

RAISERROR('Start insert of data',0,1)

-- super fast insert of 100,000 rows into a table
SELECT TOP (100000)
        RowNo   = ISNULL(CAST( ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS INT),0),        
        RandomID = NEWID() 
INTO	tempdb.dbo.random_data
FROM	master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
CROSS JOIN master.sys.all_columns ac3

SELECT @ROWS = @@ROWCOUNT, @StopStamp = CONVERT(varchar, GETDATE(),13)

RAISERROR('There are %d rows in the table. Error Code was %u. Insert completed at %s',0,1,@ROWS,@@ERROR,@StopStamp) WITH NOWAIT;

-- output results
SELECT * FROM tempdb.dbo.random_data

-- drop our table
DROP TABLE tempdb.dbo.random_data;

-- ensure it is dropped
IF OBJECT_ID('tempdb.dbo.random_data','U') IS NULL
  RAISERROR('Dropped table %s',0,1,'tempdb.dbo.random_data')
ELSE
  RAISERROR('Could not drop table %s',0,1,'tempdb.dbo.random_data')

When I run this code the debug in the messages tab of SQL Query Analyser are:

09 Jul 2015 11:37:18
Start insert at 09 Jul 2015 11:37:18
Start insert of data
There are 100000 rows in the table. Error Code was 0. Insert completed at 09 Jul 2015 11:37:18
Dropped table tempdb.dbo.random_data

As you can see you can enter multiple parameters into the RAISERROR function.

The only thing you must remember is that you cannot use functions such as GETDATE(), CONVERT() or CAST() as substitution values. They must all be literals which is why I am converting the time stamps into strings first.

I hope you also notice how fast the insert of 100,000 rows all with unique values in the 2nd column is.

It takes less than one second!

This method is much faster than any WHILE loop or CURSOR method to insert records and you should add it to your box of tricks for future use.

Some people use number tables for insert jobs like this but there is no need when you have system tables with thousands of rows within them.

The trick is to use two CROSS JOINS to to the system tables master.sys.all_columns to create the necessary rows for the insert.

CROSS JOINS are hardly used by many people but they are very useful when you need to do insert jobs like this quickly.

Note how each row is ordered sequentially from 1 to 100,000 and the 2nd column has a unique GUID inside it. This is created from the NEWID() function.

So these are just two methods for adding debug to your TSQL code and it is up to you which method you find the most useful for the job at hand.

RAISERROR function parameter substitution values

%d or %i = signed integer

%o = unsigned octal

%p = pointer

%s = string

%u = unsigned integer

%x or %X = unsigned hexadecimal

By Strictly Software 

© 2015 Strictly Software