Windows XP Run Commands and Shortcuts

Windows XP Home / Pro Run Commands and Short Cuts
How To - Click Start, Click Run and enter the command Click OK

Run commands

Calc - Calculator

Cfgwiz32 - ISDN Configuration Wizard

Charmap - Character Map

Chkdisk - Repair damaged files

Cleanmgr - Cleans up hard drives

Clipbrd - Windows Clipboard viewer

Cmd - Opens a new Command Window (cmd.exe)

Control - Displays Control Panel

Dcomcnfg - DCOM user security

Debug - Assembly language programming tool

Defrag - Defragmentation tool

Drwatson - Records programs crash & snapshots

Dxdiag - DirectX Diagnostic Utility

Explorer - Windows Explorer

Fontview - Graphical font viewer

Ftp - ftp.exe program

Hostname - Returns Computer's name

Ipconfig - Displays IP configuration for all network adapters

Jview - Microsoft Command-line Loader for Java classes

MMC - Microsoft Management Console

Msconfig - Configuration to edit startup files

Msinfo32 - Microsoft System Information Utility

Nbtstat - Displays stats and current connections using NetBios over TCP/IP

Netstat - Displays all active network connections

Nslookup- Returns your local DNS server

Ping - Sends data to a specified host/IP

Regedit - registry Editor

Regsvr32 - register/de-register DLL/OCX/ActiveX

Regwiz - Reistration wizard

Sfc /scannow - Sytem File Checker

Sndrec32 - Sound Recorder

Sndvol32 - Volume control for soundcard

Sysedit - Edit system startup files (config.sys, autoexec.bat, win.ini, etc.)

Taskmgr - Task manager

Telnet - Telnet program

Tracert - Traces and displays all paths required to reach an internet host

Winipcfg - Displays IP configuration

Management Consoles

certmgr.msc - Certificate Manager

ciadv.msc - Indexing Service

compmgmt.msc - Computer management

devmgmt.msc - Device Manager

dfrg.msc - Defragment

diskmgmt.msc - Disk Management

fsmgmt.msc - Folder Sharing Management

eventvwr.msc - Event Viewer

gpedit.msc - Group Policy -XP Pro only

iis.msc - Internet Information Services

lusrmgr.msc - Local Users and Groups

mscorcfg.msc - Net configurations

ntmsmgr.msc - Removable Storage

perfmon.msc - Performance Manager

secpol.msc - Local Security Policy

services.msc - System Services

wmimgmt.msc - Windows Management

Shortcuts

access.cpl - Accessibility Options

hdwwiz.cpl - Add New Hardware Wizard

appwiz.cpl - dd/Remove Programs

timedate.cpl - Date and Time Properties

desk.cpl - Display Properties

inetcpl.cpl - Internet Properties

joy.cpl - Joystick Properties

main.cpl keboard - Keyboard Properties

main.cpl - Mouse Properties

ncpa.cpl - Network Connections

ncpl.cpl - Network Properties

telephon.cpl - Phone and Modem options

powercfg.cpl - Power Management

intl.cpl - Regional settings

mmsys.cpl sounds - Sound Properties

mmsys.cpl - Sounds and Audio Device Properties

sysdm.cpl - System Properties

nusrmgr.cpl - User settings

firewall.cpl - Firewall Settings (sp2)

wscui.cpl - Security Center (sp2)

Windows Environment Commands

%ALLUSERSPROFILE% - Open the All User's Profile

%HomeDrive% - Opens your home drive e.g. C:\

%UserProfile% - Opens you User's Profile

%temp% Opens - temporary file Folder

%systemroot% - Opens Windows folder

Wupdmgr - Takes you to Microsoft Windows Update

General keyboard shortcuts

CTRL+C(Copy)

CTRL+X (Cut)

CTRL+Z (Undo)

DELETE (Delete)

SHIFT+DELETE (Delete the selected item permanently without placing the item in the Recycle Bin)

CTRL while dragging an item (Copy the selected item)

CTRL+SHIFT while dragging an item (Create a shortcut to the selected item)

F2 key (Rename the selected item)

CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word)

CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word)

CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph)

CTRL+SHIFT with any of the arrow keys (Highlight a block of text)

CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph)

SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document)

CTRL+A (Select all)

F3 key (Search for a file or a folder)

ALT+ENTER (View the properties for the selected item)

ALT+F4 (Close the active item, or quit the active program)

ALT+ENTER (Display the properties of the selected object)

ALT+SPACEBAR (Open the shortcut menu for the active window)

CTRL+F4 (Close the active document in programs that enable you to have multiple documents open simultaneously)

ALT+TAB (Switch between the open items)

ALT+ESC (Cycle through items in the order that they had been opened)

F6 key (Cycle through the screen elements in a window or on the desktop)

F4 key (Display the Address bar list in My Computer or Windows Explorer)

SHIFT+F10 (Display the shortcut menu for the selected item)

ALT+SPACEBAR (Display the System menu for the active window)

CTRL+ESC (Display the Start menu)

ALT+Underlined letter in a menu name (Display the corresponding menu)

Underlined letter in a command name on an open menu (Perform the corresponding command)

F10 key (Activate the menu bar in the active program)

RIGHT ARROW (Open the next menu to the right, or open a submenu)

LEFT ARROW (Open the next menu to the left, or close a submenu)

F5 key (Update the active window)

BACKSPACE (View the folder one level up in My Computer or Windows Explorer)

ESC (Cancel the current task)

SHIFT when you insert a CD-ROM into the CD-ROM drive (Prevent the CD-ROM from automatically playing)

CTRL+SHIFT+ESC (Open Task Manager) Dialog box keyboard shortcuts



If you press SHIFT+F8 in extended selection list boxes, you enable extended selection mode. In this mode, you can use an arrow key to move a cursor without changing the selection. You can press CTRL+SPACEBAR or SHIFT+SPACEBAR to adjust the selection. To cancel extended selection mode, press SHIFT+F8 again. Extended selection mode cancels itself when you move the focus to another control.



CTRL+TAB (Move forward through the tabs)

CTRL+SHIFT+TAB (Move backward through the tabs)

TAB (Move forward through the options)

SHIFT+TAB (Move backward through the options)

ALT+Underlined letter (Perform the corresponding command or select the corresponding option)

ENTER (Perform the command for the active option or button)

SPACEBAR (Select or clear the check box if the active option is a check box)

Arrow keys (Select a button if the active option is a group of option buttons)

F1 key (Display Help)

F4 key (Display the items in the active list)

BACKSPACE (Open a folder one level up if a folder is selected in the Save As or Open dialog box)

Microsoft natural keyboard shortcuts

Windows Logo+BREAK (Display the System Properties dialog box)

Windows Logo+D (Display the desktop)

Windows Logo+M (Minimize all of the windows)

Windows Logo+SHIFT+M (Restore the minimized windows)

Windows Logo+E (Open My Computer)

Windows Logo+F (Search for a file or a folder)

CTRL+Windows Logo+F (Search for computers)

Windows Logo+F1 (Display Windows Help)

Windows Logo+ L (Lock the keyboard)

Windows Logo+R (Open the Run dialog box)

Windows Logo+U (Open Utility Manager)

Accessibility keyboard shortcuts

Right SHIFT for eight seconds (Switch FilterKeys either on or off)

Left ALT+left SHIFT+PRINT SCREEN (Switch High Contrast either on or off)

Left ALT+left SHIFT+NUM LOCK (Switch the MouseKeys either on or off)

SHIFT five times (Switch the StickyKeys either on or off)

NUM LOCK for five seconds (Switch the ToggleKeys either on or off)

Windows Logo +U (Open Utility Manager)

Windows Explorer keyboard shortcuts

END (Display the bottom of the active window)

HOME (Display the top of the active window)

NUM LOCK+Asterisk sign (*) (Display all of the subfolders that are under the selected folder)

NUM LOCK+Plus sign (+) (Display the contents of the selected folder)

NUM LOCK+Minus sign (-) (Collapse the selected folder)

LEFT ARROW (Collapse the current selection if it is expanded, or select the parent folder)

RIGHT ARROW (Display the current selection if it is collapsed, or select the first subfolder)

Shortcut keys for Character Map

After you double-click a character on the grid of characters, you can move through the grid by using the keyboard shortcuts

RIGHT ARROW (Move to the right or to the beginning of the next line)

LEFT ARROW (Move to the left or to the end of the previous line)

UP ARROW (Move up one row)

DOWN ARROW (Move down one row)

PAGE UP (Move up one screen at a time)

PAGE DOWN (Move down one screen at a time)

HOME (Move to the beginning of the line)

END (Move to the end of the line)

CTRL+HOME (Move to the first character)

CTRL+END (Move to the last character)

SPACEBAR (Switch between Enlarged and Normal mode when a character is selected)

Microsoft Management Console (MMC) main window keyboard shortcuts

CTRL+O (Open a saved console)

CTRL+N (Open a new console)

CTRL+S (Save the open console)

CTRL+M (Add or remove a console item)

CTRL+W (Open a new window)

F5 key (Update the content of all console windows)

ALT+SPACEBAR (Display the MMC window menu)

ALT+F4 (Close the console) ALT+A (Display the Action menu)

ALT+V (Display the View menu)

ALT+F (Display the File menu)

ALT+O (Display the Favorites menu)

MMC console window keyboard shortcuts

CTRL+P (Print the current page or active pane)

ALT+Minus sign (-) (Display the window menu for the active console window)

SHIFT+F10 (Display the Action shortcut menu for the selected item)

F1 key (Open the Help topic, if any, for the selected item)

F5 key (Update the content of all console windows)

CTRL+F10 (Maximize the active console window)

CTRL+F5 (Restore the active console window)

ALT+ENTER (Display the Properties dialog box, if any, for the selected item)

F2 key (Rename the selected item)

CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)

Remote desktop connection navigation

CTRL+ALT+END (Open the Microsoft Windows NT Security dialog box)

ALT+PAGE UP (Switch between programs from left to right)

ALT+PAGE DOWN (Switch between programs from right to left)

ALT+INSERT (Cycle through the programs in most recently used order)

ALT+HOME (Display the Start menu)

CTRL+ALT+BREAK (Switch the client computer between a window and a full screen)

ALT+DELETE (Display the Windows menu)

CTRL+ALT+Minus sign (-) (Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing ALT+PRINT SCREEN on a local computer.)

CTRL+ALT+Plus sign (+) (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)

Microsoft Internet Explorer navigation

CTRL+B (Open the Organize Favorites dialog box)

CTRL+E (Open the Search bar)

CTRL+F (Start the Find utility)

CTRL+H (Open the History bar)

CTRL+I (Open the Favorites bar)

CTRL+L (Open the Open dialog box)

CTRL+N (Start another instance of the browser with the same Web address)

CTRL+O (Open the Open dialog box, the same as CTRL+L)

CTRL+R (Update the current Web page)

CTRL+ CTRL+P (Open the Print dialog box)

W (Close the current window)

Generat Serial Number from Select Query T-SQL

There are times when you would want to generate your own serial numbers for a query. For eg: If we take the Customers table in Northwind database, the CustomerID is not numeric Eg: 'ALFKI'.

If you want to generate your own serial number for the Customers table while displaying the records, use the ROW_NUMBER(). As mentioned in BOL "ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition"

Here is a query that will help you generate your own serial numbers for the CustomerID

SELECT (ROW_NUMBER() OVER ( ORDER BY CustomerID)) AS ID ,
CustomerID, ContactName, CompanyName, ContactTitle
FROM Customers

ROW_Number() is a magical function. It can generate serials but it requires the column based on which it generate the Order. Also it can repeat the sequence on every occurrence of the given condition.
ROW_Number() require Minimum 1 and maximum 2 data to do all the magic.

1. Column to decide the order. Multiple column can be used in csv format
2. Column to decide Partition on which Row_Number() will regenerate serial from 1.

Lets see this in action

We have tables State(stid,stname) and city(ctid, ctname)

;WITH CTE(STID,STNAME, CTID, CTNAME,STIID)AS(
SELECT *  FROM TBSTATES S JOIN TBCITIES C ON S.ID=C.STATEID
)
 
SELECT STNAME, (ROW_NUMBER() OVER( PARTITION BY STNAME ORDER BY CTNAME))CITI_SNO, CTNAME FROM CTE

This will give a column citi_sno with repetition when state will change.

Fill up the data in table and try yourself.

How to check if a date is a valid date in Sql Server 2005

At times, in our stored procedures or sql queries, we need to check if the date specified in the variables is a valid date.

Use the ISDATE() function.

The ISDATE() function determines whether the variable or the expression contains a valid date. It returns 1(true) if the input expression is a valid date; otherwise, it returns 0 (false).

For eg:
DECLARE @dt varchar(10)
SET @dt = '02/21/08'
SELECT ISDATE(@dt)
-- Returns 1

DECLARE @dt varchar(10)
SET @dt = '13/21/08'
SELECT ISDATE(@dt)
-- Returns 0 as 13 is not a valid month

How to send an email using SQL Server

With SQL Server 2005, there is no need to use MAPI client to send emails. Fellow developers who have used MAPI in the previous versions of SQL Server are well aware of the challenges it had. However in Sql Server 2005, we can now use the Database Mail to send emails.

[Note: I assume you have set up and configured Database Mail. If not, check this link Database Mail Configuration Stored Procedures to use various stored procedures required to configure Database Mail. As an alternative, you could also use the SQL Server Surface area configuration tool to configure Database Mail]

Use the following script to send a mail from your Sql Server

USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'admin@xyz.com; xyz@xyz.com; pqr@xyz.com',
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;
GO

Documentation for all available options is available at msdn
sp_send_dbmail (Transact-SQL)

SQL Injection

SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark "--". Subsequent text is ignored at execution time.

The following script shows a simple SQL injection. The script builds an SQL query by concatenating hard-coded strings together with a string entered by the user:

var Shipcity;
ShipCity = Request.form ("ShipCity");
var sql = "select * from OrdersTable where ShipCity = '"
+ ShipCity + "'";

The user is prompted to enter the name of a city. If she enters Redmond, the query assembled by the script looks similar to the following:

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond'

However, assume that the user enters the following:

Redmond'; drop table OrdersTable--

In this case, the following query is assembled by the script:

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond';drop table OrdersTable--'

The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select all records in OrdersTable where ShipCity is Redmond. Then, SQL Server will drop OrdersTable. As long as injected SQL code is syntactically correct, tampering cannot be detected programmatically. Therefore, you must validate all user input and carefully review code that executes constructed SQL commands in the server that you are using. Coding best practices are described in the following sections in this topic. Validate All Input Always validate user input by testing type, length, format, and range. When you are implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. Remember that programs designed to run in a secure environment can be copied to an nonsecure environment. The following suggestions should be considered best practices:

• Make no assumptions about the size, type, or content of the data that is received by your application. For example, you should make the following evaluation:
  
  • How will your application behave if an errant or malicious user enters a 10-megabyte MPEG file where your application expects a postal code?
  • How will your application behave if a DROP TABLE statement is embedded in a text field?
• Test the size and data type of input and enforce appropriate limits. This can help prevent deliberate buffer overruns.
• Test the content of string variables and accept only expected values. Reject entries that contain binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
• When you are working with XML documents, validate all data against its schema as it is entered.
• Never build Transact-SQL statements directly from user input.
• Use stored procedures to validate user input.
• In multitiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected and an error should be returned to the previous tier.
• Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against determined attackers. A better practice is to validate input in the user interface and at all subsequent points where it crosses a trust boundary. For example, data validation in a client-side application can prevent simple script injection. However, if the next tier assumes that its input has already been validated, any malicious user who can bypass a client can have unrestricted access to a system.
• Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.
• Do not accept the following strings in fields from which file names can be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.

When you can, reject input that contains the following characters.

Input character	Meaning in Transact-SQL
:	Query delimiter.
'	Character data string delimiter.
--	Comment delimiter.
/*......*/	Comment delimiters. Text between /* and */ is not evaluated by the server.
xp_	Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.

Use Type-Safe SQL Parameters

The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value instead of as executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside the range will trigger an exception. The following code fragment shows using the Parameters collection:

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In this example, the @au_id parameter is treated as a literal value instead of as executable code. This value is checked for type and length. If the value of @au_id does not comply with the specified type and length constraints, an exception will be thrown.

Use Parameterized Input with Stored Procedures

Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:

SqlDataAdapter myCommand =new SqlDataAdapter("LoginStoredProcedure '" + _
Login.Text + "'", conn);

If you use stored procedures, you should use parameters as their input.

Use the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown in the following code example:

SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", conn);
SQLParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",SqlDbType.VarChar, 11);
Parm.Value = Login.Text;

Filtering Input

Filtering input may also be helpful in protecting against SQL injection by removing escape characters. However, because of the large number of characters that may pose problems, this is not a reliable defense. The following example searches for the character string delimiter.

private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");
}

LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still must be escaped:

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");

Reviewing Code for SQL Injection You should review all code that calls EXECUTE, EXEC, or sp_executesql. You can use queries similar to the following to help you identify procedures that contain these statements. SELECT object_Name(id) FROM syscomments WHERE UPPER(text) LIKE '%EXECUTE (%' OR UPPER(text) LIKE '%EXECUTE (%' OR UPPER(text) LIKE '%EXECUTE (%' OR UPPER(text) LIKE '%EXECUTE (%' OR UPPER(text) LIKE '%EXEC (%' OR UPPER(text) LIKE '%EXEC (%' OR UPPER(text) LIKE '%EXEC (%' OR UPPER(text) LIKE '%EXEC (%' OR UPPER(text) LIKE '%SP_EXECUTESQL%'

Wrapping Parameters with QUOTENAME() and REPLACE()

In each selected stored procedure, verify that all variables that are used in dynamic Transact-SQL are handled correctly. Data that comes from the input parameters of the stored procedure or that is read from a table should be wrapped in QUOTENAME() or REPLACE(). Remember that the value of @variable that is passed to QUOTENAME() is of sysname, and has a maximum length of 128 characters.

@variable	Recommended wrapper
Name of a securable	QUOTENAME(@variable)
String of ≤ 128 characters	QUOTENAME(@variable, '''')
String of > 128 characters	REPLACE(@variable,'''', '''''')

When you use this technique, a SET statement can be revised as follows:

--Before:

SET @temp = N'select * from authors where au_lname=''' + @au_lname + N''''

--After:

SET @temp = N'select * from authors where au_lname=''' + REPLACE(@au_lname,'''','''''') + N''''

Injection Enabled by Data Truncation

Any dynamic Transact-SQL that is assigned to a variable will be truncated if it is larger than the buffer allocated for that variable. An attacker who is able to force statement truncation by passing unexpectedly long strings to a stored procedure can manipulate the result. For example, the stored procedure that is created by the following script is vulnerable to injection enabled by truncation.

CREATE PROCEDURE sp_MySetPassword
@loginname sysname,
@old sysname,
@new sysname 
AS
 
-- Declare variable.
-- Note that the buffer here is only 200 characters long.
 
DECLARE @command varchar(200)
-- Construct the dynamic Transact-SQL.
-- In the following statement, we need a total of 154 characters
-- to set the password of 'sa'.
-- 26 for UPDATE statement, 16 for WHERE clause, 4 for 'sa', and
-- 2 for
-- quotation marks surrounded by QUOTENAME(@loginname):
-- 200 – 26 – 16 – 4 – 2 = 154.
-- But because @new is declared as a sysname, this variable can
-- only hold
-- 128 characters.
-- We can overcome this by passing some single quotation marks in
-- @new.
 
SET @command= 'update Users set password='
+ QUOTENAME(@new, '''')
+ ' where username='
+ QUOTENAME(@loginname, '''')
+ ' AND password = '
+ QUOTENAME(@old, '''')
 
-- Execute the command.
EXEC (@command)
 
GO

By passing 154 characters into a 128 character buffer, an attacker can set a new password for sa without knowing the old password.

EXEC sp_MySetPassword 'sa', 'dummy',

'123456789012345678901234567890123456789012345678901234567890123 45678901234567890123456789012345678901



For this reason, you should use a large buffer for a command variable or directly execute the dynamic Transact-SQL inside the EXECUTE statement.

Truncation When QUOTENAME(@variable, '''') and REPLACE() Are Used

Strings that are returned by QUOTENAME() and REPLACE() will be silently truncated if they exceed the space that is allocated. The stored procedure that is created in the following example shows what can happen.

CREATE PROCEDURE sp_MySetPassword
@loginname sysname,
@old sysname,
@new sysname 
AS
 
-- Declare variables.
DECLARE @login sysname
DECLARE @newpassword sysname
DECLARE @oldpassword sysname
DECLARE @command varchar(2000)
-- In the following statements, the data stored in temp variables
-- will be truncated because the buffer size of @login,
-- @oldpassword,
-- and @newpassword is only 128 characters, but QUOTENAME()
-- can return
-- up to 258 characters.
SET @login = QUOTENAME(@loginname, '''')
SET @oldpassword = QUOTENAME(@old, '''')
SET @newpassword = QUOTENAME(@new, '''')
-- Construct the dynamic Transact-SQL.
-- If @new contains 128 characters, then @newpassword will be
-- '123... n
-- where n is the 127th character.
-- Because the string returned by QUOTENAME() will be truncated,
-- it can be made to look like the following statement:
-- UPDATE Users SET password ='1234. . .[127] WHERE username='
-- other stuff here
 
SET @command = 'UPDATE Users set password = '
+ @newpassword
+ ' where username ='
+ @login + ' AND password = ' + @oldpassword;
 
-- Execute the command.
EXEC (@command)
 
GO

Therefore, the following statement will set the passwords of all users to the value that was passed in the previous code.

EXEC sp_MyProc '--', 'dummy',

'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678'

You can force string truncation by exceeding the allocated buffer space when you use REPLACE(). The stored procedure that is created in the following example shows what can happen.

CREATE PROCEDURE sp_MySetPassword
-- Declare variables.
DECLARE @login sysname
DECLARE @newpassword sysname
DECLARE @oldpassword sysname
DECLARE @command varchar(2000)
-- In the following statements, data will be truncated because
-- the buffers allocated for @login, @oldpassword and @newpassword
-- can hold only 128 characters, but QUOTENAME() can return
-- up to 258 characters.
SET @login = REPLACE(@loginname, '''', '''''')
SET @oldpassword = REPLACE(@old, '''', '''''')
SET @newpassword = REPLACE(@new, '''', '''''')
-- Construct the dynamic Transact-SQL.
-- If @new contains 128 characters, @newpassword will be '123...n
-- where n is the 127th character.
-- Because the string returned by QUOTENAME() will be truncated, it
-- can be made to look like the following statement:
-- UPDATE Users SET password='1234…[127] WHERE username='
-- other stuff here
 
SET @command= 'update Users set password = ''' + @newpassword + ''' where username=''' + @login + ''' AND password = ''' + @oldpassword + '''';
 
-- Execute the command.
EXEC (@command)
 
GO

As with QUOTENAME(), string truncation by REPLACE() can be avoided by declaring temporary variables that are large enough for all cases. When possible, you should call QUOTENAME() or REPLACE() directly inside the dynamic Transact-SQL. Otherwise, you can calculate the required buffer size as follows.

For @outbuffer = QUOTENAME(@input), the size of @outbuffer should be 2*(len(@input)+1). When you use REPLACE() and doubling quotation marks, as in the previous example, a buffer of 2*len(@input) is enough. The following calculation covers all cases: While len(@find_string) > 0, required buffer size = round(len(@input)/len(@find_string),0) * len(@new_string) + (len(@input) % len(@find_string))

Truncation When QUOTENAME(@variable, ']') Is Used

Truncation can occur when the name of a SQL Server securable is passed to statements that use the form QUOTENAME(@variable, ']'). The following example shows this. CREATE PROCEDURE sp_MyProc @schemaname sysname, @tablename sysname, AS -- Declare a variable as sysname. The variable will be 128 -- characters. -- But @objectname actually must allow for 2*258+1 characters. DECLARE @objectname sysname SET @objectname = QUOTENAME(@schemaname)+'.'+ QUOTENAME(@tablename) -- Do some operations. GO When you are concatenating values of type sysname, you should use temporary variables large enough to hold the maximum 128 characters per value. If possible, call QUOTENAME() directly inside the dynamic Transact-SQL. Otherwise, you can calculate the required buffer size as explained in the previous section.

@loginname sysname,

@old sysname,

@new sysname AS