Thứ Bảy, 14 tháng 10, 2017

Convert DB query to JSON

DECLARE @Shopping xml;
SET @Shopping = (SELECT ExpenseDate, ExpenseAmount, c.CategoryName, ExpenseDesc
FROM SB_ExpenseItems e
JOIN SB_ExpenseCategories c on (c.CategoryID = e.CategoryID)
FOR XML path, root)

--Ref: https://www.codeproject.com/Articles/815371/Data-Parsing-SQL-to-JSON
-- Function for Conversion | XML to JSON

SELECT Stuff( 
  (SELECT * from 
    (SELECT ',
    {'+ 
      Stuff((SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+
                    b.c.value('text()[1]','NVARCHAR(MAX)') +'"'
             
             from x.a.nodes('*') b(c) 
             for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
        ,1,1,'')+'}'
   from @Shopping.nodes('/root/*') x(a) 
   ) JSON(theLine) 
  for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,'')

Thứ Năm, 8 tháng 5, 2014

Problem "Container does not stretch to fit floated contents" and how to use ClearFix technique to solve it?


Getting this problem?


Problem: A parent div with child element which has float property affects the parent div or makes it disappear.

Collapsing and Clearfix
One typical issue when using floats, is that parent elements don’t resize to fit their floated children. That makes sense, as they are removed from the normal flow. The solution to this is usually through a technique called Clearfix hack. A clearfix is a technique for an element to automatically clear after itself, thus preventing collapse.

Hack-free approach
Overflow: hidden is very popular method to clear floats without adding extra markup. But it becomes undesirable in few circumstances where placing the absolute positioned element, then it cuts off the element. So we can make use of different methods.


Normally, you can apply something like the following to the outer div:

  1. height: 1%; overflowhidden;  

In most modern browsers, the container will now stretch to fit the floated contents.

Clear Parent Element In CSS Using Clearfix
In some circumstances, especially when support for ancient browsers is required, there’s also a generally trouble-free hack.

The clearfix hack is used to clear floated divisions (divs) without using structural markup. 



  1. /* new clearfix */  
  2. .clearfix:after {  
  3.     visibilityhidden;  
  4.     displayblock;  
  5.     font-size: 0;  
  6.     content" ";  
  7.     clearboth;  
  8.     height: 0;  
  9.     }  
  10. * html .clearfix             { zoom: 1; } /* IE6 */  
  11. *:first-child+html .clearfix { zoom: 1; } /* IE7 */  

Yes it’s ugly, but it works very well, enabling designers to clear floats without hiding overflow and setting a width or floating (nearly) everything to get the job done.

Now simply add:
  1. class="clearfix"  

Note: Some people uses the clearfix technique in slightly different way, for example:
  1. .clearfix:after {  
  2.      content".";   
  3.      displayblock;   
  4.      height: 0;   
  5.      clearboth;   
  6.      visibilityhidden;  
  7. }  

Notice the line containing the content: "."; property. I have found that the period (or dot) specified in quotes has a nasty tendency to break certain layouts. By adding a literal dot after the .clearfix division (i.e., via the .clearfix:after selector), the clearfix hack creates a stumbling block for certain browsers. And not just for Internet Explorer — depending on the layout, even Firefox will choke a layout upon tripping on an :after-based pseudo-dot.

Use a space instead of a dot to prevent breaking layouts
The solution to this subtle design chaos? Replace the literal dot with a single blank space: "content: " "; — this trick has proven successful so consistently that I now use it as the default property in every clearfix hack.

References:

Thứ Bảy, 26 tháng 4, 2014

How to make a resizable TextArea with resizer handle bar?


There is already a good JQuery plugin for adding a grippie handle at the bottom of TextArea. Like below:


It works pretty good in every browsers. I believe it is the simplest sample. You can find such sample named "ResizableTextArea.html" in the attached source code.

However, when putting it into your code, make sure it is well tested in different test cases. There are 2 main test cases you have to fix immediately:

  1. When resize the browser window, the handle bar doesn't auto resize!
  2. When resize the TextArea by dragging the south-east icon, the handle bar doesn't auto resize!

To resolve this, I implemented a second sample named "ResizableTextArea2.html" using JQuery-ui plugin. It works pretty well and the good thing is that you can replace any handle you like through CSS. Only drawback to this sample is that, it's quite not good looking to dock the handle bar to bottom of TextArea that is already affected by jquery ui css. 

To resolve problem of sample2, I went on with more simple sample by using mousemove event handler. Personally, I prefer the last solution because it's quite simple with native JQuery and will not create any conficts. If you want to go with sample2, you'll have to do a lot of customization for JQuery-ui plugin. It's daunting task that I don't want to risk my tremendous efforts for such task of litte value. Sometimes, enough is better! :-)

Download source code here: Resizable TextArea with handle bar

Happy Coding,
Henry Pham,





Thứ Sáu, 18 tháng 4, 2014

Best Practices for SEO-Friendly optimization

These SEO tips have helped many e-commerce websites increase sales and revenue by large multiples, and can help you boost sales:
  • HTML easily parsed by search spiders
  • Lightweight HTML, separate from CSS
  • For e-commerce site, set title and META details per product, per page, and per category...
  • SEO friendly product links, category links, brand links, search links, and page links...
  • Automatically generated HTML sitemap and XML sitemap for Google Webmaster Tools
  • Correct use of robots.txt file, H1 through H6 tags, and NOFOLLOW attribute
  • PageRank not passed to irrelevant pages
  • Product image alt text
  • Use lists instead of paragraphs.
You can see why using Lists and Heading tags (h1, h2...h6) can increase your page ranking in eyes of Google

  • Include internal sub-headings and they should include your keyword phrase. Use heading tags for your sub-heads, and repeat your keyword phrase.
  • URL - Be short and go static. The best URLs are human readable without lots of parameters, numbers and symbols. Using technologies like mod_rewrite for Apache and ISAPI_rewrite for Microsoft, you can easily transform dynamic URLs like this http://moz.com/blog?id=123 into a more readable static version like this: http://moz.com/blog/google-fresh-factor. Even single dynamic parameters in a URL can result in lower overall ranking and indexing.
  • Prevent cache.
    A cache buster is a string of code inserted in a page or a tag to prevent browsers and proxies to fetch a file (most often an ad) from the cache.
    Using the Google cache feature makes it difficult for search engines to interpret relevancy.
    Note: Image cachebuster changes too frequently might damage SEO. The cachebuster in image urls changes too frequently, even when the image itself hasn't changed. This causes search engines to believe that the image has changed when it hasn't, which results in the images not getting indexed.

Thứ Tư, 16 tháng 4, 2014

Cross-Site-Scripting-XSS Attacking

Cross-site scripting (XSS) is a type of computer security vulnerability typically found in Web applications. XSS enables attackers to inject client-side script into Web pages through form fields or URL.

XSS happens when a server accepts input from the client and then blindly writes that input back to the page. Most of the protection from these attacks involves escaping the output, so the Javascript turns into plain HTML.

Below are sequence diagrams to help figure out how XSS attacks your Web application through URL:





Example 1: 
http://example.com/unsafepage?name=Rumplestiltskin<script>alert('1')

For example have a guestbook or comment widget on your homepage and a client posts some javascript code which fx redirects you to another website or sends your cookies in an email to a malicious user or it could be a lot of other stuff which can prove to be real harmful to you and the people visiting your page.

Tip: If the website doesn't properly sanitize the input/output, an attacker could add script to a comment, which would then be displayed and executed on the computers of anyone who viewed the comment.

Example 2: 
In this scenario we have an attacker who is on another computer and has access to your Web site, but not as admin. His objective is to set up a XSS attack to steal the admin session cookie, send it to him, and use it to gain access to the admin account.

Assuming that the server is running at the IP address of 192.168.1.16. The attackers computer is running Backtrack, which has the IP address of 192.168.1.14.



These are simple examples. There's a lot more to it and a lot of different types of XSS attacks.

Best Practices for avoiding XSS Attacking:
  • Understand inputs and outputs for the code you are reviewing. Dataflow analysis is a powerful mechanism for finding security bugs. Understand every source of data in the code you are reviewing as well as where the data will end up. How much trust you are willing to give the source as well as the ultimate destination of the data both have a major impact on the level of data validation the code should have.
  • Your application needs to ensure that all variable output in a page is encoded before being returned to the end user. Encoding variable output substitutes HTML markup with alternate representations called entities. The browser displays the entities but does not run them. For example,

Thứ Ba, 15 tháng 4, 2014

How to keep footers at the bottom of the page

Description: This trick is not new, but it's always helpful for any people who want to design a docking footer with their Web site. Thank author (Mathew James Taylor) for very good article. Also pass on thank-you words to Mr. Pham Dinh Truong for good workaround with middle layer (see comment below this article).

Please note that you run into same problem even you use table to replace div!

When an HTML page contains a small amount of content, the footer can sometimes sit halfway up the page leaving a blank space underneath. This can look bad, particularly on a large screen. Web designers are often asked to push footers down to the bottom of the viewport, but it's not immediately obvious how this can be done.
A diagram describing the footer problem and the ideal solution
When I first ditched tables for pure CSS layouts I tried to make the footer stay at the bottom but I just couldn't do it. Now, after a few years of practice I have finally figured out a neat way to do it. My method uses 100% valid CSS and it works in all standards compliant browsers. It also fails gracefully with older browsers so it's safe to use on any website.

The main features

  • Works in all modern, standards compliant browsers

    Compatible browsers: Firefox (Mac & PC), Safari (Mac & PC), Internet Explorer 7, 6 & 5.5, Opera and Netscape 8
  • Fails gracefully on older browsers

    Older non-standards compliant browsers position the footer under the content as per normal. We can't help it if people are using an out of date browser, all we can do is reward people who have upgraded by giving them a better browsing experience through progressive enhancement.
  • Longer content pushes the footer off the page

    On long pages with lots of content the footer is pushed off the visible page to the very bottom. Just like a normal website, it will come into view when you scroll all the way down. This means that the footer isn’t always taking up precious reading space.
  • 100% valid CSS with no hacks

    The CSS used in this demo is 100% valid and contains no hacks.
  • No JavaScript

    JavaScript is not necessary because it works with pure CSS.
  • iPhone compatible

    This method also works on the iPhone and iPod Touch in the mobile Safari browser.
  • Free to download

    Simply save the source code of my demo page and use it however you like.

There is only one limitation

You must set the height of the footer div to something other than auto. Choose any height you like, but make sure the value is specified in pixels or ems within your CSS. This is not a big limitation, but it is essential for this method to work correctly.
If you have a lot of text in your footer then it's also a good idea to give the text a bit more room at the bottom by making your footer a bit deeper. This is to cater for people who have their browser set to a larger text size by default. Another way to solve the same problem is to set the height of the footer in em units; this will ensure that the footer grows in size along with the text. If you only have images in your footer than there's nothing to worry about – just set your footer height to a pixel value and away you go.

So how does it work?

It's actually not that complicated. There are two parts to it - the HTML and the CSS.

The HTML div structure

  1. <div id="container">  
  2.    <div id="header"></div>  
  3.    <div id="body"></div>  
  4.    <div id="footer"></div>  
  5. </div>  

There are only four divs required for this to work. The first is a container div that surrounds everything. Inside that are three more divs; a header, a body and a footer. That's it, all the magic happens in the CSS.

The CSS
  1. html,  
  2. body {  
  3.    margin:0;  
  4.    padding:0;  
  5.    height:100%;  
  6. }  
  7. #container {  
  8.    min-height:100%;  
  9.    position:relative;  
  10. }  
  11. #header {  
  12.    background:#ff0;  
  13.    padding:10px;  
  14. }  
  15. #body {  
  16.    padding:10px;  
  17.    padding-bottom:60px;   /* Height of the footer */  
  18. }  
  19. #footer {  
  20.    position:absolute;  
  21.    bottom:0;  
  22.    width:100%;  
  23.    height:60px;   /* Height of the footer */  
  24.    background:#6cf;  
  25. }  

And one simple CSS rule for IE 6 and IE 5.5:
  1. #container {  
  2.    height:100%;  
  3. }  

The html and body tags
The html and body tags must be set to height:100%; this allows us to set a percentage height on our container div later. I have also removed the margins and padding on the body tag so there are no spaces around the parameter of the page.

The container div

The container div has a min-height:100%; this will ensure it stays the full height of the screen even if there is hardly any content. Many older browsers don't support the min-height property, there are ways around it with JavaScript and other methods but that is out of scope for this article. The container div is also set to position:relative; this allows us to absolutely position elements inside it later.

The header div

There is nothing unusual with the header. Make it whatever color and size you like.

The body div

The body is quite normal too. The only important thing is it must have a bottom padding that is equal to (or slightly larger than) the height of the footer. You can also use a bottom border if you prefer but a margin won't work.

The footer div

The footer has a set height in pixels (or ems). The div is absolutely positioned bottom:0; this moves it to the bottom of the container div. When there is little content on the page the container div is exactly the height of the browser viewport (because of the min-height:100%;) and the footer sits neatly at the bottom of the screen. When there is more than a page of content the container div becomes larger and extends down below the bottom of the viewport - the footer is still positioned at the bottom of the container div but this time you need to scroll down to the end of the page to see it. The footer is also set to width:100%; so it stretches across the whole page.

The IE 6 & IE 5.5 CSS

Older versions of Internet Explorer don't understand the min-height property but lucky for us the normal height property behaves exactly the same way in these old Microsoft browsers, that is, it will stretch to 100% height of the viewport but if the content is longer it will stretch even further. We simply expose this 100% height rule to Internet Explorer only by using IE conditional comments. View the source on the demo to see how this is done.

So there you have it... A simple, valid way to make the footer get down! I hope you find it useful.

Thứ Hai, 14 tháng 4, 2014

What is smart way to search through Store Procedures to replace the old table name with new name?

Consider a scenarioSearch through every stored procedure for a string and possibly replace it, like a standard Find/Replace function. 

Many of us sooner or later run into a tedious task of modifying object names in all stored procedures on the server. A column name was changed, a table was renamed, a database on a linked server was moved to another location, etc. 

For example: I have a lot of SPs that are using a table called "Clients".  Now I want to rename my table to Customers, how can I change all the SP without having to open each one manually?

First, you might need to find all the objects where you have used "Clients".

SELECT DISTINCT o.name
FROM sysobjects o
INNER JOIN syscomments c ON c.Id = o.Id
WHERE --xtype = 'p' AND
 category = 0
  AND c.text LIKE '%Clients%'
ORDER BY o.name

This will list out all the objects where you have used this table.

Second, you might need to script out all the procedures and then use search and replace in text editor to modify them. You might need to write script to automate the tasks. All you need to do is change search and replace strings and it will generate the scripts for you. Here is the complete script:

-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON DECLARE @sqlToRun VARCHAR(1000),
                                 @searchFor VARCHAR(100),
                                            @replaceWith VARCHAR(100) -- text to search for
SET @searchFor = '[MY-SERVER]' -- text to replace with
SET @replaceWith = '[MY-SERVER2]' -- this will hold stored procedures text
DECLARE @temp TABLE (spText VARCHAR(MAX)) DECLARE curHelp
CURSOR FAST_FORWARD
FOR -- get text of all stored procedures that contain search string
-- I am using custom escape character here since i need to espape [ and ] in search string

SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
FROM syscomments
WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'
ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '

OPEN curHelp

FETCH next FROM curHelp INTO @sqlToRun

WHILE @@FETCH_STATUS = 0
BEGIN
   --insert stored procedure text into a temporary table
   INSERT INTO @temp
   EXEC (@sqlToRun)
   
   -- add GO after each stored procedure
   INSERT INTO @temp
   VALUES ('GO')
   
   FETCH next FROM curHelp INTO @sqlToRun
END

CLOSE curHelp
DEALLOCATE curHelp

-- find and replace search string in stored procedures 
-- also replace CREATE PROCEDURE with ALTER PROCEDURE
UPDATE @temp
SET spText = REPLACE(REPLACE(spText,'
  CREATE PROCEDURE', '
  ALTER PROCEDURE'),@searchFor,
                    @replaceWith) 
  SELECT spText
  FROM @temp -- now copy and paste result into new window
-- then make sure everything looks good and run
GO