Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Simple Rules to Prevent SQL Injections

Introduction

Previously I written around 7 computer security related articles (mostly on web security and cyber security), and this is my first article or post on RDBMS data or database application security.

In this article I will explain in short what are sql injections and simple rules to prevent sql injections, I want to say thanks to my friend Vaibhav Shringi (DB expert and IITian) for his help to prepare this post.

What is SQL injection?

SQL injection is a type of security exploit in which the attacker adds SQL code to a Web form input box or any other manner to gain access to resources or make changes to data.

How to Prevent SQL Injection Attacks? 

Simple Rules to Prevent SQL Injections

1. Sanitize the Input:

It's absolutely vital to sanitize user inputs to insure that they do not contain dangerous codes, whether to the SQL server or to HTML itself.

We should always attempt to allow only required characters approach not to stuff “Bad characters”

There is really no benefit in allowing characters that could not be valid, and rejecting them early - presumably with an error message - not only helps forestall SQL Injection, but also catches mere typos early rather than stores them into the database.

Better prevention: White List Input Validation

2. Escape/Quotesafe the input:

We can’t sanitize the inputs which allows special characters eg. “Bill O'Reilly” is a valid name.

Always use QUOTENAME() function in SQL statements if user input are required in In-line queries.

3. Use Bound Parameters:

Though quote-safing is a good mechanism, we're still in the area of "considering user input as SQL", and a much better approach exists: bound parameters, which are supported by essentially all database programming interfaces.

Example:

            PreparedStatement ps = connection.prepareStatement(
             "SELECT email FROM member WHERE name = ?");
            ps.setString(1, formField);
            ResultSet rs = ps.executeQuery();

 

This is probably the single most important step one can take to secure a web application.

 

4. Limit database permissions and segregate users:

The web application ought to use a database connection with the most limited rights possible: query-only access to the members table, and no access to any other table. If required can move to higher rights after successful login. 

It prevents unauthorized updates/delete/drop operations.
We should not use “SA” or same level rights’ users.

 5. Use stored procedures for database access:

Use stored procedures for performing access on the application's behalf, which can eliminate SQL entirely. By encapsulating the rules for a certain action - query, update, delete, etc. - into a single procedure, it can be tested and documented on a standalone basis and business rules enforced.

Example

DB SP:

      Create PROCEDURE [dbo].[ReadUserDetails]
                -- Add the parameters for the stored procedure here
          @userName varchar(50)
         AS
         BEGIN
           SELECT * FROM [UserDetails] where userName= @userName
         END

 

C# code that’s use SP:

  public static DataTable ExecuteSelectCommand(string StoredProcedureName)
        {
            // SqlCommand cmd = null;
            var table = new DataTable();
                using (var con = new SqlConnection(GetConnectionString()))
                {
                    con.Open();
                    using (var cmd = new SqlCommand(StoredProcedureName, con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        //cmd.CommandText = commandName;
                        SqlDataAdapter da = null;
                        using (da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(table);
                        }
                    }
                }
            return table;
        }


6. Isolate the webserver:

Even having taken all these mitigation steps, it's nevertheless still possible to miss something and leave the server open to compromise.

Isolated webserver with limited network pinholes can assure limited access to other servers in case of full webserver control.

 

7. Configure error reporting:

The default error reporting for some frameworks includes developer debugging information, and this cannot be shown to outside users. Imagine how much easier a time it makes for an attacker if the full query is shown, pointing to the syntax error involved.

This information is useful to developers, but it should be restricted - if possible - to just internal users.



धन्यवाद मित्रो !! 

Thanks Friends 

SQL server 2016 Transfer Maintenance Plan from One Server to Another

 Hi this is Vaibhav Shringi, This is my 3rd article on SQL server 2016, in this article, will explain in detail how to transfer maintenance plan from one server to another.

In the following example, I will use two servers NMK135 and VS12345 for explanation and sample users Vadmin and nmk12345.

Transfer Maintenance Plan from One Server to Another

1. Connect to Source Integration server

Connect to Integration Services in SQL Server Management Studio (Where Maintenance plan already present Say SERVER-1)


2. Export Maintenance plan package from SERVER-1.

Connect with appropriate permissions and export the “DBMaintenancePlan” package.


3. Select Package Location as File System and browse for Package path to save “DBMaintenancePlan.dtsx” file.

4. Save and OK to complete the export process in SERVER-1.

5. Transfer the newly exported file to new server file system (say SERVER-2).

6. Transfer any required Rebuild Indexes and UpdateStatistics SPs from SERVER-1 to SERVER-2(if any).

7. Connect to Integration Services of SERVER-2. (use step 1). Import the previously exported package.


8. Connect to Database Engine of SERVER-2


9. Verify imported Maintenance Plan on SERVER-2.


10. Modify Plan to change some config entries.


11. Follow below steps ONLY IF WANT TO change Job Schedule Timing.

Click on Calendar button present right side of “Schedule” tab. Change appropriately and save the changes.


Below Step 12 to 14 are for “Nightly_DB_maintenance” Subplan.

Click on Nightly_DB_maintenance Subplan to see below screen.


12. Verify Rebuild and update statistics tasks.

13. Verify Database Backup Location and database names.

      => Double click on “Full Backup Database task”.
      => Verify Folder location.


14. Verify Backup File Cleanup Period.

     => Double click on “CleanUp BackUp Files”.
     => Verify File Age. (Prefer to have 2 weeks) 


Below Step 15 is for “Every2Hrs_DB_Tran_Bkp” Subplan.

Click on Nightly_DB_maintenance Subplan to see below screen


15. Verify Transactional backup location and databases names.

      =>Double click on “Back Up Database Task”
      =>Verify Folder location. 



16. Save the Maintenance plan and close it. 

How to VERIFY Maintenance Plan Jobs?

VERIFY Maintenance Plan Jobs:

1. Open property window of both the jobs (right click on Job name)

  a. DBMaintenancePlan.Every2Hrs_DB_Tran_Bkp
  b. DBMaintenancePlan.Nightly_DB_Maintenance


2. Job owner:

    => If face any access related issue with Vadmin then change Job owner to “SA”.
    => We are facing this issue in VS12345 because of SQL agent service default configuration. 

3. Review Schedule Time etc.


धन्यवाद मित्रो !! 

Thanks Friends