SQL Injection avoidance in SQL Server

Intro about SQL Injection :
             
         [ Content taken from my company documentations]

        The expansion of the Internet has made web applications become a part of everyday life. As a result the numbers of incidents which exploit web application vulnerabilities are increasing. A large percentage of these incidents are SQL Injection attacks which are a serious security threat to databases with potentially sensitive information. Therefore, much research has been done to detect and prevent these attacks and it resulted in a decline of SQL Injection attacks. However, there are still methods to bypass them and these methods are too complex to implement in real web applications. 

         [ Content taken from my company documentations]

A SQL Injection attack takes place when an attacker tries to gain access to a database by supplying special input to the web-site, which in turn sends the innocent input as an SQL-query to the Database Management System. The growing use of web-applications for business purposes has given motivation to attackers to explore the possibilities and exploit these type of attacks.

Am using  a user defined stored procedure which will take care of validating the input values provided by the users.

[code]



CREATE FUNCTION dbo.SQLInjectionCheck_UserDefinedFun
(@VenkatString varchar(max))

RETURNS BIT
AS
BEGIN
DECLARE @Suspect_ValBit bit

SET @VenkatString = ' ' + @VenkatString
IF (PATINDEX('% xp_%' , @VenkatString ) <> 0 OR
PATINDEX('% sp_%' , @VenkatString ) <> 0 OR
PATINDEX('% DROP %' , @VenkatString ) <> 0 OR
PATINDEX('% GO %' , @VenkatString ) <> 0 OR
PATINDEX('% INSERT %' , @VenkatString ) <> 0 OR
PATINDEX('% UPDATE %' , @VenkatString ) <> 0 OR
PATINDEX('% DBCC %' , @VenkatString ) <> 0 OR
PATINDEX('% SHUTDOWN %' , @VenkatString )<> 0 OR
PATINDEX('% ALTER %' , @VenkatString )<> 0 OR
PATINDEX('% CREATE %' , @VenkatString ) <> 0OR
PATINDEX('%;%' , @VenkatString )<> 0 OR
PATINDEX('% EXECUTE %' , @VenkatString )<> 0 OR
PATINDEX('% BREAK %' , @VenkatString )<> 0 OR
PATINDEX('% BEGIN %' , @VenkatString )<> 0 OR
PATINDEX('% CHECKPOINT %' , @VenkatString )<> 0 OR
PATINDEX('% BREAK %' , @VenkatString )<> 0 OR
PATINDEX('% COMMIT %' , @VenkatString )<> 0 OR
PATINDEX('% TRANSACTION %' , @VenkatString )<> 0 OR
PATINDEX('% CURSOR %' , @VenkatString )<> 0 OR
PATINDEX('% GRANT %' , @VenkatString )<> 0 OR
PATINDEX('% DENY %' , @VenkatString )<> 0 OR
PATINDEX('% ESCAPE %' , @VenkatString )<> 0 OR
PATINDEX('% WHILE %' , @VenkatString )<> 0 OR
PATINDEX('% OPENDATASOURCE %' , @VenkatString )<> 0 OR
PATINDEX('% OPENQUERY %' , @VenkatString )<> 0 OR
PATINDEX('% OPENROWSET %' , @VenkatString )<> 0 OR
PATINDEX('% EXEC %' , @VenkatString )<> 0)
BEGIN
SELECT @Suspect_ValBit = 1
END
ELSE
BEGIN
SELECT @Suspect_ValBit = 0
END
RETURN (@Suspect_ValBit)
END
GO

 -----------     -----------    
SELECT dbo.SQLInjectionCheck_UserDefinedFun
('SELECT * FROM HumanResources.Department')

--------------------- The result is "0"--------------------- 

SELECT dbo.SQLInjectionCheck_UserDefinedFun
(';SHUTDOWN')

--------------------- The result is "1"--------------------- 

SELECT dbo.SQLInjectionCheck_UserDefinedFun
('DROP HumanResources.Department')

--------------------- The result is "1"--------------------- 

[/code]


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech,
www.kaashivinfotech.com
A very best Inplant Training and Internship Providers in Chennai

Ntile feature in SQL Server

I got a peculiar query from one of the user to fetch data from the tables.

 Here is the query, He needs to fetch 50% bottom data to the top and 50% top data to the bottom  of the table.      

  I've used Ntile option to manipulate the desired output.


create table #t(col1 varchar(6),col2 varchar(6))
--drop table #t
insert into #t values('a','s')
insert into #t values('a','g')
insert into #t values('b','h')
insert into #t values('b','r')
insert into #t values('s','j')
insert into #t values('s','k')
insert into #t values('e','o')
insert into #t values('e','p')
insert into #t values('q','x')
insert into #t values('q','c')
select * from #t

Select col1,col2 From (
    Select NTILE(2) Over (Order by col1 Desc) as HalfNumber, *
    From #t) as NtileTable
where HalfNumber = 1
union all
Select col1,col2 From (
    Select NTILE(2) Over (Order by col1 Desc) as HalfNumber, *
    From #t) as NtileTable
where HalfNumber = 2

Cheers,


Venkatesan Prabu .J
Head, KaaShiv InfoTech
A very best Inplant Training and Internship Providers in Chennai

Execute Stored Procedure inside another Stored Procedure

A small piece of code to execute procedure inside another procedure


create table venkat_Table( id int,
val varchar(1000) )

insert into venkat_table values (100,
'Venkat From KaaShiv InfoTech')

create procedure VenkatFirstProc
as begin
  select * from venkat_Table
end 


create Procedure VenkatSecondProc
as begin

  exec VenkatFirstProc
  
  end
  
  exec VenkatSecondProc

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

SQL Server division returns zero

While trying a small piece of code. I've happened to face a problem with division by any value on an integer is returning zero. 


Tried changing the destination value as decimal but still no effect.






print  @PrevEff  print @PrevCost print @PrevUsefulCost  
print @CurrEff
declare @finalcost decimal(10,2)
set @finalcost = @PrevEff  / @PrevUsefulCost  
print @finalcost

----------The above statements provided 0 as the output.....


Now, I've tried changing the manipulation part as Casting to float and process it... OOPS... it's working like a charm.....

print  @PrevEff  print @PrevCost print @PrevUsefulCost  
print @CurrEff
declare @finalcost decimal(10,2)
set @finalcost =  CAST(@PrevEff AS float) / CAST(@PrevUsefulCost  AS float)
print @finalcost


 Cheers,
Venkatesan Prabu .J

MDX Query to fetch the Measures available in the cube

Here is the query to fetch the possible measures available in the Knowledge Cubes 



SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
    [MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
 WHERE CUBE_NAME  ='MDXProjectCube'
 ORDER BY [MEASUREGROUP_NAME]

MDX Query to fetch the possible attributes in a Cube

Here is the MDX query to fetch all the possible attributes related to a cube in the database.


SELECT [CATALOG_NAME] as [DATABASE],

      CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
      LEVEL_CAPTION AS [ATTRIBUTE],
      [LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME],
      [LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME]
FROM $system.MDSchema_levels
WHERE CUBE_NAME  ='MDXProjectCube'  
AND LEVEL_NAME <> '(All)'
order by [DIMENSION_UNIQUE_NAME]

MDX Query to fetch all the Cubes in the database

Here is the query to fetch the available cubes in the SSAS database.


SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME
FROM $system.MDSchema_Cubes
WHERE CUBE_SOURCE=1

Cheers,

Venkatesan Prabu.J

Fetch tables from Knowledge Cubes in SQL Server Analysis Services

I've just found a new way of accessing the tables from the cube. Below is the query used to fetch the tables linked with a cube.



SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS 
WHERE [CUBE_NAME]='SourceDataView'

In addition, this view provides more information about the dimensions, table involved in the dimensions.

Cheers,
Venkatesan Prabu .J

Collation Conflicts and Settings in SQL Server

System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the UNION operation.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 m

---------------------------------------------------------------------------------------------------------------------------------------

Solution: The issue is with the collation settings of the database Right click on the database options and change the collation. Your query will work like a charm.

:-)

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

Error with MODI in C#

Here is one of the sample error received while using MODI library.

Retrieving the COM class factory for component with CLSID {40942A6C-1520-4132-BDF8-BDC1F71F547B} failed due to the following error: 80040154.

Solution:

  1. May be the libraries were not registered properly in the system (Interop.Modi)
  2. Microsoft document Imaging software is not installed properly in your system. Run your Microsoft office setup  -> go to add/remove features -> Microsoft office features- > Microsoft document imaging. Right click and set the options to install in the local machine.


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech

Cross join feature in SQL Server

One of the superb feature of sql server is application of cross joins. 

It provides an option of multiple select statement into a single insert statement. I've happened to work in sql server for getting lot of informations from different select statement and I need to insert into a single insert statement.. In this case, my cross join helps me a lot.

 Here is the syntax for the same.

SELECT
  f1.col1, f2.col2, f3.col3, f4.col4
FROM
  (select count(1)  AS col1
 from SystemBasedUser() where ID like '%MS_SQL%') AS F1
 CROSS JOIN
 (select count(1) AS col2
 from SystemBasedUser() where ID like '%MS_POLI%') AS F2
 CROSS JOIN
 (select count(1) AS col3
 from SystemBasedUser() where ID like '%MS_SMO%') AS F3
 CROSS JOIN
 (select count(1) AS col4
 from SystemBasedUser() where ID like '%MS_AGEN%') AS F4

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

Security related information - System logins in sql server

Here is the query to fetch security related information from the master database:

The below query will fetch and retrieve the security related information from the database. It will provide the list of users and their access nature. Execute it and see the magic :-)

SELECT
[name] as LoginName,
LOGINPROPERTY ([name] , 'DefaultDatabase') as DefaultDatabase,
LOGINPROPERTY ([name] , 'DaysUntilExpiration') as DaysUntilExpiration,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsExpired'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsExpired,
(CASE ISNULL(LOGINPROPERTY ([name] , 'IsLocked'),0) WHEN 0 THEN 'False' ELSE 'True' END) as IsLocked,
LOGINPROPERTY ([name] , 'PasswordLastSetTime') as PasswordLastSetTime,
LOGINPROPERTY ([name] , 'PasswordHashAlgorithm') as PasswordHashAlgorithm
FROM master..syslogins

Cheers,
Venkatesan Prabu .J 
Head, KaaShiv InfoTech  

Fix: f5 is not working in windows forms

Problem f5 is not working in windows forms - Unable to start the windows forms :

   Recently, I've faced this problem while trying to access my windows forms.

  1. Created windows forms

  2. Run the application

  3. Stopped

  4. Re-run the application.

Oops, I couldn't run my app once again. Wait for another 2 minutes. Now, it's running perfectly.

Possible Solution:

  1. May be, Ur windows forms exe is still in the running process. Go and kill the process. 

  2. Check for the Application Experience service. It should run.

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

www.kaashivinfotech.com

Just for Fun

I've happened to see a joke series in facebook. Wish to share with my blog readers.

Smart Doctor!
Q: Doctor, I've heard that cardiovascular exercise can prolong life. Is this true?
A: Heart only good for so many beats, and that it... Don't waste on exercise. Everything wear out eventually. Speeding up heart not make you live longer; it like saying you extend life of car by driving faster. Want to live longer? Take nap.

Q: Should I reduce my alcohol intake?
A: Oh no. Wine made from fruit. Brandy distilled wine, that mean they take water out of fruity bit so you get even more of goodness that way. Beer also made of grain. Bottom up!

Q: How can I calculate my body/fat ratio?
A: Well, if you have body and you have fat, your ratio one to one. If you have two body, your ratio two to one.

Q: What are some of the advantages of participating in a regular exercise program?
A: Can't think of single one, sorry. My philosophy: No pain...good!

Q: Aren't fried foods bad for you?
A: YOU NOT LISTENING! Food fried in vegetable oil. How getting more vegetable be bad?

Q : Will sit-ups help prevent me from getting a little soft around the middle?
A: Oh no! When you exercise muscle, it get bigger. You should only be doing sit-up if you want bigger stomach.

Q: Is chocolate bad for me?
A: You crazy?!? HEL-LO-O!! Cocoa bean! Another vegetable! It best feel-good food around!

Q: Is swimming good for your figure?
A: If swimming good for figure, explain whale to me.

Q: Is getting in shape important for my lifestyle?
A: Hey! 'Round' is shape!

Well... I hope this has cleared up any misconceptions you may have had about food and diets.

And remember:
Life should NOT be a journey to the grave with the intention of arriving safely in an attractive and well-preserved body, but rather to skid in sideways - Chardonnay in one hand - chocolate in the other - body thoroughly used up, totally worn out and screaming "WOO-HOO, what a ride!!"

AND.....

For those of you who watch what you eat, here's the final word on nutrition and health. It's a relief to know the truth after all those conflicting nutritional studies.

1. The Japanese eat very little fat and suffer fewer heart attacks than Americans.

2. The Mexicans eat a lot of fat and suffer fewer heart attacks than Americans.

3. The Chinese drink very little red wine and suffer fewer heart attacks than Americans.

4. The Italians drink a lot of red wine and suffer fewer heart attacks than Americans...

5. The Germans drink a lot of beer and eat lots of sausages and fats and suffer fewer heart attacks than Americans.

CONCLUSION: Eat and drink what you like. Speaking English is apparently what kills you...............

[FIX]Current thread must be set to single thread apartment (STA) mode before OLE calls can be made. Ensure that your Main function has STAThreadAttribute marked on it. This exception is only raised if a debugger is attached to the process.

While working with Windows Application, I've come up with a small error while executing the code. Below is the error string

Current thread must be set to single thread apartment (STA) mode before OLE calls can be 
made. Ensure that your Main function has STAThreadAttribute marked on it. This exception is only raised if a debugger is attached to the process.

Solution 1: Add the key word [STAThread] before the main function
To over come the above specified error. 


   [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Login());
        }
Solution 2: Delete the files available in the bin\debug folder and rebuild the solution.

Solution 3: Check the task manager for the exe running in the background. Kill it. Your application will work as usual.

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech 
www.kaashivinfotech.com

Get File Extension from C#

While working in C#, I came to know about finding the file extension and wish to share the same with my blog readers.....

///// Sample code to get the file extension.

Add the namespace using system.io;

        string filename = @"C:\Users\venkat\Documents\venkat.doc";

 string extension = Path.GetExtension(p);
 if (extension  == ".doc")
 {
      messagebox.show ( " It's a word document ");
 }

Note: Please check the case sensitiveness

Cheers,

Venkatesan Prabu .J

Director - Kaashiv InfoTech - www.kaashivinfotech.com

Dynamic controls using C# in windows forms

Here is the small piece of coding to do some Inheritance concept in C#.

Here is the problem,

  1. I want to write custom code to create controls on my windows forms page.

  2. Instead of Button class, I need to use inheritance to create my own button class and create objects.

  public class MyButton : Button
        {
            public MyButton()
                : base()
            {
                // set whatever styling properties needed here
                ForeColor = Color.Red;
                BackColor = Color.Green;
                 Location = new System.Drawing.Point(269, 96);
            }
        }

        public class MyButton1 : MyButton
        {
            public MyButton1()
                : base()
            {
                // set whatever styling properties needed here
                ForeColor = Color.Red;
                BackColor = Color.Green;
                Location = new System.Drawing.Point(269, 96);
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            MyButton1 btn = new MyButton1();
            btn.Name = "MyButton";
            this.Controls.Add(btn);
        }
    }

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech

GridView1_RowCommand event is not firing

Here is a small problem, which I've faced in C# windows forms.

GridView1_RowCommand is not firing

Solution: 

 In this case, you need to go with disabling the view state property of the form.

Cheers,

Venkatesan Prabu .J

Head, KaaShiv InfoTech, Chennai

Make 3d chart in C#

Here is the simple steps to enable or include 3d chart in C#

1. Add your chart control in the windows forms.
2. Enable the chart control.
3. As usual, add the code which i have posted in my previous post.
4. Add the below line to modify your 2d chart into 3d chart.


chart1.ChartAreas[0].Area3DStyle.Enable3D = true;

Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech
www.kaashivinfotech.com

Error in chart control in C#

Error:
A chart element with the name 'Default' could not be found in the 'SeriesCollection'.

Solution: 

You need to create a series element for a chart before accessing it.

          Series series1 = this.chart3.Series.Add("Default");
            chart3.Series["Default"].Points.DataBindY(myReader1, "OrderQty");
         

Complete coding to draw a chart in C#


  string myConnectionString = @"Data Source=XXXX;Integrated Security=true;Initial Catalog=DBName";

            // Define the database query    
            string mySelectQuery = "SELECT Top 5 OrderQty,ProductID,SpecialOfferID, UnitPrice   FROM [SalesOrderDetail]";

            // Create a database connection object using the connection string    
            SqlConnection myConnection = new SqlConnection(myConnectionString);

            // Create a database command on the connection using query    
            SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);

            // Open the connection    
            myCommand.Connection.Open();

            // Create a database reader    
            SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            Series series = this.chart1.Series.Add("Default");

            chart1.Series["Default"].Points.DataBind(myReader, "OrderQty", "ProductID", "Tooltip=SpecialOfferID, Label=UnitPrice{C2}");
            chart1.ChartAreas[0].Area3DStyle.Enable3D = true;

            // Close the reader and the connection

Cheers,
Venkatesan Prabu. J
Head, KaaShiv InfoTech, Chennai.

get specified format of the date from datetime picker

Here is a minor syntax to extract the date information from datetimepicker in windows forms c#

string VenkatDate = dateTimePicker1.Value.ToShortDateString();

To get the date in specified format:

string VenkatDate = dateTimePicker1.Value.ToString("yyyy-MM-dd");

Cheers,

Venkatesan Prabu. J

Head, KaaShiv InfoTech

www.kaashivinfotech.com

Image Height and width in C#

Code to get the height and width of an image.


System.Drawing.Image objImage = System.Drawing.Image.FromFile("C:\venkat.gif");
width = objImage.Width;
height = objImage.Height;  

Cheers,
Venkatesan prabu .J
http://www.facebook.com/KaaShivInfoTech

Remove drawn object in C# windows forms

Questions:

How can I delete (clear) lines that were made useing Graphics.DrawLine() ?
 
Drawn a circle in windows form. I want the remove the drawn object in C# windows forms.
Pen pen = new Pen(Color.Black, 3); Graphics gr = this.CreateGraphics(); gr.DrawEllipse(pen, 5,5,20,20);


Quick Answer:

this.Invalidate();

Cheers,

Venkatesan Prabu . J

https://www.facebook.com/KaaShivInfoTech