How to use conditional CASE statement in Oracle WHERE clause to retrieve data

Posted on by
Reply

Here is the table structure that I need to query:

UserID

Type

Name

Department

Status

Classification

Enrollment_Status

Where “Type” and “Department” are foreign keys of the other two tables. The query criteria are:

If the user type is “Student” (type ID = 1), then only retrieve enrolled students (Enrollment_Status = ‘EN’);

If the user type is not “Student” (type ID <> 1), then check Classification field and only retrieve data with Classification <> ‘R’

It is a perfect scenario for using CASE statement in WHERE clause, and here is the complete query in Oracle PL/SQL:

SELECT    U.UserID, U.NAME, 

          D.DEPARTMENT,  U.STATUS, 

          U.CLASSIFICATION

FROM      Users U, Departments D, UserType T    

WHERE CASE WHEN (T.ID = '1') AND  (U.ENROLLMENT_STATUS = 'EN') THEN 1

           WHEN (T.ID <> '1') AND (U.CLASSIFICATION <> 'R') THEN 1

           ELSE 0

      END = 1

AND  U."TYPE" = T.ID 

AND (U.DEPARTMENT = D.DEPARTMENT_CODE (+) )

ORDER BY UserID;

For more information about CASE statement in MS SQL, check here: http://msdn.microsoft.com/en-us/library/ms181765.aspx

For more information about CASE statement in Oracle PL/SQL, check here:

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/04_struc.htm#484

[UPDATE]: I just found out that .NET TableAdapter does not recognize the CASE statement above, thought the above query runs well in Oracle SQL Developer. After further investigation, I realize the above query can be changed to this:

SELECT    U.UserID, U.NAME, 

          D.DEPARTMENT,  U.STATUS, 

          U.CLASSIFICATION

FROM      Users U, Departments D, UserType T    

WHERE 

((T.ID = '1' AND  U.ENROLLMENT_STATUS = 'EN') OR (T.ID <> '1' AND U.CLASSIFICATION <> 'R'))

AND  U."TYPE" = T.ID 

AND (U.DEPARTMENT = D.DEPARTMENT_CODE (+) )

ORDER BY UserID;

And TableAdapter will recognize the above query and execute it without any error.

More free bonus from SugarSync cloud service

Posted on by
Reply

If you already use SugarSync, then you know that after you sign up an account (either Free or Paid account), if you finish five tasks under Getting Started tab in your account, then you will get 125 MB bonus storage. If you think 125 MB bonus is not good enough, then here is the good news. Started June 6th, SugarSync lets you earn 125 MB bonus for each task you finish, so the total bonus will be 625 MB when you finish all five tasks.

SugarSync bonus storage

 

What about those who already finished the five tasks before the change? The good news is they are eligible too! If you already finished the tasks before, then just log back into your account, and you will be prompted for this bonus. All you need to do is to accept the bonus, and you don’t need to do the tasks again. How nice. For more information for this new bonus, check this post: http://www.sugarsync.com/blog/2011/07/06/free-storage-come-and-get-it/

 

If you haven’t used SugarSync, or you haven’t heard of SugarSync, SugarSync is a secure online backup and file synchronization service. You can back up your important files on SugarSync cloud storage and also synchronize them with your other devices, such as computer and smart phone. For more information about SugarSync and other similar services, you can read my blog post http://www.codingbeaver.com/myblog/index.php/2011/04/29/drop-dropbox-and-pick-up-sugarsync/

If you decide to use SugarSync, you can use this referral link to sign up. With this link you will get 500 MB bonus storage when you complete the registration. So your total bonus storage will be 500 MB + 625 MB (if you finish the five tasks) = 1125 MB!

Sign up with this referral link to get 500 MB bonus:

https://www.sugarsync.com/referral?rf=e3izqm2hfvy70

And I will get 500 MB bonus if you sign up using the above link. Thank you in advance.

Web site hacked

Posted on by
Reply

My site was hacked on June 28th, 2011 around 8:00pm. How I found out? Here is the story.

When I tried to access my site after coming back from a vacation, I noticed there is a PHP error at the bottom of the page as this:

PHP Warning:  Cannot modify header information – headers already sent by (output started at /home/{username}/public_html/blog/wp-content/plugins/all-in-one-seo-pack/aioseop.class.php:221) in /home/{username}/public_html/blog/wp-content/themes/gray-and-square/footer.php(2) : eval()’d code(216) : eval()’d code on line 1

I thought the theme file is somehow corrupted, so I tried to log into my site to fix the theme and I was surprised that my site kept telling me my credentials were wrong. Finally I decided to reset my password, but was shocked when I got the password reset email showing my username is “user”. No wonder I could not log into my site.

After I reset my password, I changed the username back to my previous one directly from the database end because WordPress does not allow anyone to change their username.

The change of username, not by me, makes me believe that my site was hacked, so I did more investigation in the file system. There was no surprise to find out that a suspicious file was uploaded to my public_html folder called us.php. This file gave the hacker full control of my site, and here is a screen shot of the execution of the file:

BlogSiteHacked

The timestamp of the folders show that the “imgs” folder was modified, and indeed there is a file that will turn my site into a phishing site. Obviously, there is some security hole(s) that enabled the hacker to upload us.php to my site and then gained the full control of the site. The question is where the security hole(s) came from? I have been keeping up with the latest WordPress all the time, whenever a new version is released, I will update my site to use it. The only possibility, then, is the third-party plugins I have been using.

Here are the plugins I use in my WordPress blog:

  • Akismet
  • All in One SEO Pack
  • cforms
  • MCEComments

To me, the vulnerability most likely came from All in One SEO Pack, though I am not 100% sure. And it seems that someone else has already complained about the security vulnerabilities in this plugin. You can check here.

Now my site is back and running with All in One SEO Pack disabled. I will be more careful in the future when choosing a third-party plugin for my site.

FireFox 5.0 released

Posted on by
Reply

As promised in FireFox’s new rapid release development cycle, Mozilla released a new version of FireFox web browser today: FireFox 5.0, about three months after the release of FireFox 4.0 on March 22nd, 2011.

Go ahead upgrade your current FireFox or download and install FireFox if you are not using it (really? You are not using FireFox?), and enjoy the security and feature it brings to you.

Block unwanted web tracking via Ghostery

Posted on by
Reply

Do you know when you visit a web site, you behavior might be monitored and tracked? If I tell you that there are six sites will be monitoring you when you visit MSNBC web site (www.msnbc.com), will you believe it?

Thanks to a nice browser plugin called Ghostery, you can find all those hidden sites that are tracking you. I highly recommend everyone to download and install it, and the plugin is available for Internet Explorer, FireFox, Google Chrome, and Safari.

Note: lots of web sites are using Google Analytics to track web site visits, so if you use Ghostery and enable tracking block, you need to remember to uncheck Google Analytics from the the blocking list.

Here is a screen shot displaying all web sites that are tracking you on MSNBC web site.

GhosteryFireFox

Send a laughter via ETrade BabyMail

Posted on by
Reply

I bet lot of people are familiar with E*Trade’s famous talking baby commercials, but do you know that last week E*Trade launched a fun, free web site allowing user to create and send personalized and animated talking E*Trade Baby message to anyone? You can use the built-in E*Trade Baby images or upload your own images, then type or record a brief message, or choose from one of the built-in messages, then you are ready to send it to anyone you would like via using email, blog, Facebook, or mobile. It can be used for any occasions, I guess, but as Father’s Day is around the corner, it would be a wonderful idea to send your dad a laughter via E*Trade BabyMail.

Here is the web site: http://www.etradebabymail.com

Here is my E*Trade BabyMail: http://www.etradebabymail.com/?mId=40132417.3

Have fun!

Windows XP mode could not be restored because of either host processor mismatch or lack of hardware assisted virtualization support in the system

Posted on by
1

If you use Windows XP Mode and make any change in your BIOS, then you may notice that next time when you try to launch Windows XP Mode, you will get an error message as this:

Windows XP mode could not be restored because of either host processor mismatch or lack of hardware assisted virtualization support in the system.

The reason is that by default when you close Windows XP Mode, Windows Virtual PC will put Windows XP Mode in hibernation. If you make change in your BIOS, then Windows Virtual PC will think the hibernation file is corrupted and throw the above error. The fix is easy: find the hibernation file, delete it (or rename it), then restart Windows XP Mode.

The hibernation file in Windows 7 is located at C:\Users\{UserID}\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines, the name of the file is “Windows XP Mode.vsv”. You can either change the extension or delete the file, then restart Windows XP Mode, it will create a new .vsv file.

To prevent this error from happening again in the future, you can change the virtual pc option from Hibernation to Shutdown.

1. Open Virtual PC.
2. Click on Tools and then click on Settings.
3. Under the Close tab, change the option from Hibernate to shutdown.
4. Restart your computer and start your Virtual PC for the changes to take effect.

Drop Dropbox and pick up SugarSync

Posted on by
2
IMPORTANT UPDATES

I just found out that Windows Live Mesh does NOT encrypt user files stored on their server, here is the original blog post talking about how the original Live Mesh was designed and implemented three years ago: http://blogs.msdn.com/b/livemesh/archive/2008/05/29/behind-live-mesh-authorization-and-encryption.aspx, and to my disappointment, three years later, Windows Live Mesh still has not implemented data encryption to protect user files. Here is the question I asked and the answer I got from Widnows Live Help Center: http://windowslivehelp.com/thread.aspx?threadid=6c278f92-9ab2-4917-88eb-9dc03ef74947

 

SugarSycn will encrypt user files and no one else can decrypt your files except yourself, according to their reply to my email inquiry:
[UPDATE, June 16th, 2011]: SugarSync apologized to me that the email above from their support staff has incorrect information regarding the encryption. The encryption key is NOT stored in user’s password, and SugarSync IS able to decrypt user’s file. Here are some quoted messages from SugarSync:

We store each user file with a different key. The keys are not based on user passwords in any form; we couldn’t provide the functionality we do (sharing, photo galleries, etc.) otherwise. Encryption keys are different for each user.

I understand the concern that some customers have about the possibility of SugarSync employees accessing data stored on our servers. This happens in very rare cases during troubleshooting with the permission of the customer; only a select few people have that level of access.

With respect to warrants and subpoenas, we abide by the laws of the State of California and the United States.

Let me know if you have any further questions.

Thank you!

==================

Hello Jeffrey-

Our agent did indeed give you incorrect information, and I apologize for that. Encryption keys are not stored with your password.

From your ticket, it looks like you wanted to know if SugarSync can decrypt files if needed. The answer is yes, we can decrypt files. This occurs only when we’re troubleshooting issues with the customer’s consent.

I’m sorry again for the miscommunication. Let me know if you have any further questions.

Thank you!

=================

So I apologize to those who read my post and had a wrong understanding of SugarSync’s security.

==================End of Update==========================

 

Since the security issue of Dropbox was discovered and the contradiction of their security claims in their new terms of service and on their web site was criticized, I have decided to drop Dropbox and to find an alternative.
Here are some on-line synchronization and backup services I have used or am still using. I personally trust their security policy, so I will not discuss anything related security.

Windows Live Mesh

Before Dropbox, I have been using Microsoft’s Windows Live Mesh since its beta version and I am still using it.

Pros:

  • Service is stable and the synchronization speed is very fast.
  • Generous free space for on-line synchronization. It integrates with Windows Live SkyDrive (25 GB free space if you have a Live ID) in which 5 GB of your SkyDrive space can be used for on-line synchronization.
  • PC-to-PC synchronization. Windows Live Mesh lets you synchronize a folder on one PC running Windows Live Mesh client directly with another PC running Live Mesh client without using the on-line storage. Because of this, there is no size limit on the data you synchronize as long as your hard drive is big enough to hold the data.
  • Remote desktop access. It is a killer feature. Among all the PC’s running Windows Live Mesh client, you can log into a PC from another PC remotely, just like the Remote Desktop Connection feature in Windows, but uses different protocol. It is very handy if your computers are behind firewall, RDC port is blocked, or you don’t know how to set up port forwarding, etc, all you need to do is to allow the computer to be accessed remotely. Then when you launch Windows Live Mesh client from a PC to connect another PC, you will see a screen like this under the “Remote” tab:
    WindowsLiveMeshRemoteAccess
    After the connection is setup and you type in your credentials, you can remotely access your PC (the scree shows I remotely logged into my home PC from my office PC via Windows Live Mesh):
    WindowsLiveMeshRemoteLogin
  • Automatic synchronization of program settings. If you turn on Program Settings synchronization feature in Windows Live Mesh, then you can have your IE favorites synchronized between computers, and also have your Microsoft Office styles, templates, email signatures, and custom dictionary synchronized as well.
    WindowsLiveMeshProgramSettingsSync
  • Allow multiple Live Mesh synchronization on one PC.You do NOT need to install multiple Windows Live Mesh clients on one PC if you are sharing the PC with other people, and it does NOT require each user to have a separate desktop account. You can sign in with different Live ID to set up different sync folders (Dropbox does not allow this).

Cons:

  • Windows Live Mesh only supports Windows and Mac. No Linux support is available at this moment though it has been requested.
  • Windows Live Mesh web interface is very poor at the time of writing. The current web interface allows you to view and download your files stored in the 5 GB SkyDrive synced storage, but you cannot add new files to the storage folder. The old beta version of Live Mesh allows user to upload files to the on-line synchronized folder, but the current version somehow removed this feature, and it drives me crazy.
  • Although you can remotely log into a computer running Live Mesh client via the web interface, it requires you to install an ActiveX control, which means you must use Internet Explorer.
  • No versioning support.

Conclusion:

I will continue using Windows Live Mesh and hope the future release of the web interface will be better and allow user to add files to the SkyDrive synced storage through the web UI. The lacking of Linux support does not bother me very much as long as I can upload/add file to the SkyDrive synced storage.

 

SpiderOak

SipderOak is a online backup and synchronization service.  It does everything Dropbox does plus online backup feature. It has various plans, including 2 GB free space plan and other paid plans if more space is needed. You can also earn more free space by referring friends to use SpiderOak.

Pros:

  • Multi-platform support. SpiderOak supports Windows, Mac, Linux and mobile OS.
  • Online file backup and accidental file deletion protection.
  • Zero-knowledge privacy environment. SpiderOak does not store user’s password, so it is the user’s responsibility to remember password. Your data is encrypted on SpiderOak’s server and only accessible by you because only you know your password.

Cons:

  • Long learning curve. The SpiderOak client UI is not straight forward and it took me quite some time to learn how to use SpiderOak. For example, you will have to set up a backup folder (or more) before you can set up sync folders. To set up synchronization between computers, you will have to give a name for each synchronization. To share files, you will have to create a ShareID and RoomKey, then you can send either of them to the person you would like to share with. To me, this is too complicated.
    SpiderOakClientUI
    SpiderOak Sync UI:
    SpiderOakClientUISync
    SpiderOak Share UI:
    SpiderOakClientUIShare
  • Unstable service. I tried to sync files between a Windows PC and an Ubuntu PC, and sometimes after I deleted a file from the Ubuntu PC, the file would not be deleted from the Windows PC, though the client showed all files are synced. It also happened to me a couple times that when I added a file to sync, the file was mysteriously deleted after the synchronization.
  • Slow upload and sync speed. I noticed that the upload speed for online backup and the sync speed are slow, even with my gigabit connection.
  • You cannot reset your password if you forget it. If you want to change your password, you will have to change it in the SpiderOak client. However, if you forget your password, all you will get is a “Password Hint” if you set up one when you sign up. If you don’t’, then you are dead. You may get some help by contacting their support team, but I have never tried to contact them, so I don’t know if they can help you reset your password.
  • Smaller free

Conclusion:

I would not recommend SpiderOak at this moment because of the unstable service. I may give it another try in the future when the service is more stable.

 

Memopal

Memopal is online backup and storage software. It allows you to access your file, or share your files, but it does not support file synchronization. I tried for some time and uninstalled because it does not have file synchronization feature. Besides, it leaves a shortcut in the device list in Windows 7, very annoying. You will have to search your registry for “Memopal” to remove it. Just for that, I will not recommend Memopal. Here is a screen shot of the issue I am talking about after the uninstallation.
MemoPalResidual

 

SugarSync

SugarSync provides the similar services as SpiderOak does: online backup, synchronization, and sharing. It provides various service plans, including a 5 GB free plan and other paid plans if more space is needed. You can also earn more free space by referring friends to use SugerSync.

Pros:

  • Service is stable and speed is fast (sync speed is a little bit slower than Windows Live Mesh).
  • Generous free space for online backup and synchronization. SugarSync gives you 5 GB free space as Windows Live Mesh does.
  • Easy to use SugarSync client user interface.
  • Upload and sync via email.
  • Support mobile device.
  • User friendly web interface. You can access your file, share your file, or sync your file via the web interface you don’t have the client installed.
  • Versioning with the ability to restore. SugarSync stores up to 5 versions of a file plus the current version, so if a file is accidently deleted during the collaboration, you can restore the file to the right version.
    SugarSyncClientUI

Cons:

  • Only support Windows and Mac platform. There is no Linux support at this moment, thought Linux support is on the to-do list.
  • Cannot sync Outlook .pst, Quicken, and Quickbooks. System folders cannot be synced either.

Conclusion:

I like SugarSync and recommend it to everyone who is looking for an online backup and sync application. If you use this link to sign up for a 5 GB free plan, you will get a 500 MB bonus (of course, I will get a 500 MB bonus) before 5/31/2011, after 5/31/2011, the bonus will become 250MB. https://www.sugarsync.com/referral?rf=e3izqm2hfvy70

After you sign up, do remember to log into your SugarSync account and click “Getting Started” tab, then make sure you finish all the five tasks listed there and you will earn an additioanl 250 MB bonus.

To sum up, my recommendation is:

1. SugarSync

2. Windows Live Mesh (if in the future Windows Live Mesh has a better web UI and implement data encryption, I may give Widnows Live Mesh higher rank than SugarSync because of the remote desktop connection feature.)

DropBox on fire

Posted on by
Reply

DropBox has been in the spotlight since they silently changed their terms of service (TOS). The updated TOS basically says, to be compliant with the US law, they would give customer’s file (after decrypting it) to the government when requested legally. This change seems to be very reasonable, because companies like Microsoft, Google, Yahoo, etc. would do the same thing, but the real problem lies in the boldest claim they have on their web site: “Dropbox employees aren’t able to access user files, and when troubleshooting an account they only have access to file metadata (filenames, file sizes, etc., not the file contents)”. It clearly contradicts to the updated TOS. So DropBox employees do have access to user files, but why they still made that claim on their web site? No matter a lie or a misunderstanding, they definitely shot themselves in the foot.

DropBoxBoldClaim

I started using DropBox about a year ago, and honestly, I was attracted by this claim and totally trusted DropBox by putting many files there. Now I don’t trust them any more and removed many sensitive files of mine from DropBox. I will still use DropBox service for storing some regular files, but for those sensitive files, I use Windows Live Mesh.  Of course, Windows Live Mesh would also give customer’s files to government when requested legally, but at least Windows Live Mesh didn’t lie and said no one else is able to access your file.

Another reason I moved my files to Windows Live Mesh is that it turned out that DropBox is not as secure as they claimed to be, check this post, and it has a detailed explanation on the insecure authentication method that DropBox uses.

If you are a DropBox user, make sure to encrypt your file yourself (use TrueCrypt), don’t rely on DropBox to protect your data, at least at this moment, until they change their authentication method.

Speed up transferring domain registration away from GoDaddy

Posted on by
1

Last week I finally decided to transfer this domain away from GoDaddy to a different registrar. I thought the process should be smooth and painless, but it turned out not. My site suffered several days of down time, as a result, lost a lot of traffic. Here are some experiences I would like to share with you:

1. My domain was not hosted by GoDaddy, and I changed the DNS nameservers before the transfer is complete. This is a very bad decision, DO NOT change it until the transfer is complete in order to minimize the down time.

2. My domain registration was private, so I had to first cancel the private registration first.

3. My domain registration at GoDaddy was set to Locked, I had to unlock it before the transfer can go to the next step. Even though I got the authorization code from GoDaddy, but since the domain is locked, it would not get transferred.

4. After I unlock the domain, and approved the domain transfer authorization email, now the transfer is officially started.

5. If you are a GoDaddy user, DO NOT just wait like I did for 4 days hoping for the “automatic process” to complete, you should log into your GoDaddy account, then go to Domain Management, then hover your mouse over Domain tab and choose Pending Transfers. Find your pending domain, then click on the small icon says “Accept or decline transfer” or something like that, then select “Accept” and click OK. This manual authorization will force the domain to be transferred within hours.

NOTE: once a domain is transferred, it cannot be transferred within 60 days, unless you transfer it back to the original registrar.