Adding & Modifying Column in MSQL Server 2008

In this post we will see some scenarios while updating or adding columns to your table in microsoft SQL Server 2008. At times it will also help you in the Microsoft SQL Server 2005 and some previous versions.

Scenario 1:

I have set all the relationship perfectly, and some changes need to be made to the table with out loosing any previous relationships.

Step 1 : First of all take the backup of your table data by executing following query

SELECT * INTO Temp FROM Table1

Step 2 : Now we have our data backed up now you can script the table by selecting the option of “Drop and Create To” from left panel.
“Do not run the query”. Just keep it open the query window.

You can see that your data has been backed up or not by executing
SELECT * FROM Temp

Step 3 : In case if you have any relationship, then delete those by going to design mode and Right click on any of the field and select Relationships. Delete all relationship one by one.

Save the changes and go to previous “Drop and Create To” query window and select only the create part as shown in figure and execute selected query by pressing execute button as highlighted in red box.

Also note that in case you want to change to any column definition or you want to add new column by itself then do it before executing query. So that you will make the changes and will not loose any relationships.

Then scroll down and select only ALTER TABLE part (remaining part except create table part of the query) and execute the query.

Step 4 : Now final step is to take the backed up data to original table.
You can do this by executing following query.


INSERT INTO Table1
SELECT * FROM Temp


Note : In case of columns are different from previous version of Table1 then you have to specify those column names like this
INSERT INTO Table1 (Column1, Column2)
SELECT Column1, Column2 FROM Temp

I would like to also thanks Husain Kachwala, who helped me in some these steps to be successful.

How to download and install Windows 7 Language Packs (MUI packs)

Language packs or MUI packs are used to change the default language of Windows 7 to a language of your choice. For example, download and installing the French language pack will translate all the menus and options in Windows 7 into French.

This feature is only available in Windows 7 Enterprise and Ultimate. You can download these language packs via Windows Update. Google works as well. The hard part is installing them, which is outlined in the walkthrough below:

Installing the Language Pack:

Start off by accessing the Control Panel through the Start Menu

Then select Change Display Challenge under the Clock, Language, and Region Box. If you’re in Classic View, select Regional and Language Options.

Make sure you’re on the “Keyboards and Languages” tab and click on Install/Uninstall Languages

Select the 2nd option and Browse to your DVD drive or where you had previously extracted the ISO. You only need to navigate to the directory in which the language pack is in. For some reason, the ISO doesn’t show up as a selectable option. (Microsoft may offer these language packs as optional updates in the near future.)

Click Open and Windows will automatically find the language pack. Accept the EULA and Install the Language Pack. This can take quite a while. Once you’re done, you’ll be asked if you want to switch to the installed language pack. Whenever you switch language packs, you will have to log off and log back in for the changes to take into effect.

In the future when you want to change language packs that have already been installed, head back to the “Keyboards and Languages” tab under Regional and Language Options and you should be able to select from a menu of different language packs. Just want to note that Windows 7 Beta actually switched to my new language pack without me having to log off even though it had asked me to.

SQL Injection in Stored Procedure & Preventing from the same

Following is the small example of creating a stored procedure.

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

CREATE PROC sp_login (@loginid nvarchar(25),@password nvarchar(25))
AS
DECLARE @SQLString VARCHAR(500)
DECLARE @loginid VARCHAR(64)
DECLARE @password VARCHAR(64)

/* Build the SQL string once.*/

SET @SQLString = ‘SELECT * from cust_users WHERE login_id = ‘+ ””+@loginid+”” + ‘AND password = ‘+ ””+@password+””

EXECUTE sp_executesql @SQLString

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

Your ASP.NET Code would look like this:

oCmd.CommandText = “sp_login”;
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add( “@loginId”, strUserName);
oCmd.Paramerters.Add( “@password”, strPassword);
oCon.Open();
string result = (string)oCmd.ExecuteScalar();
oCon.Close();

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

If the user input is as follows:
loginId = ‘ OR 1=1 –
password = junk

SQL injection will not work and ASP.NET will throw an exception

“Unclosed quotation mark after the character string ‘ OR 1=1 — and password=junk’.
Incorrect syntax near ‘ OR 1=1 — and password=junk’.”

In this case you can use

loginID = ” OR 1=1–

password = junk

Two single quotations are used to complete where clause with null condition and OR is used to make the condition true always.

If you use sp_executesql this will definitely leads to the SQL Injection.

See more on this http://msdn.microsoft.com/en-us/library/ms188001.aspx

Solution :

Instead one should use the same stored procedure which he has created, for passing parameters.

exec sp_login ‘param1′, ‘param2′

param1 – would be loginID

param2 – would be password

And you are stored procedure would look like this i.e. with out sp_executesql

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

CREATE PROC sp_login

@loginid VARCHAR(64)
@password VARCHAR(64)

AS

BEGIN
SELECT * FROM cust_users WHERE loginid=@loginid AND password=@password
END

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

This will avoid the possible SQL Injection