SQL Server connection strings

SQL ODBC connection strings

Standard Security:

Driver={SQLServer};Server=yourServerName;Database=yourDatabaseName;Uid=yourUsername;Pwd=yourPassword;

Trusted connection:

Driver={SQLServer};Server=yourServerName;Database=yourDatabaseName;Trusted_Connection=yes;

SQL OLE DB connection strings

SQL Server 2005, 2008

System.Data.OleDb.OleDbConnection

Standard Security:

Provider=SQLOLEDB;Data Source=yourServerName;Initial Catalog=yourDatabaseName;UserId=yourUsername;Password=yourPassword;

Trusted connection:

Provider=SQLOLEDB;Data Source=yourServerName;Initial Catalog=yourDatabaseName;Integrated Security=SSPI;

SQL OleDbConnection .NET strings

Standard Security:

Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;

Trusted connection:

Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;

SQL SqlConnection .NET strings

Standard Security:

1. Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;
2. Server=Your_Server_Name;Database=Your_Database_Name;UserID=Your_Username;Password=Your_Password;Trusted_Connection=False

Trusted connection:
1. Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;
2. Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=True;

Ways to counter SQL Injection

Here are a few suggestions to counter the problem of sql injections.

Database Permissions

Set the permissions on the database username / password as tightly as possible.  If you are displaying data, there is no need for the user to have insert or update permissions into the database.  One solution is to have two usernames / passwords.  One would have select permissions, and would be used only for display.

The other would have select, insert and update permissions used only for forms that require data to be stored in the database.

Test all data input

All form data and all url query strings should be tested.

For example, if you are passing data using a query string any record id’s are usually integer, so test that they are actually integer values with a function such as isumeric in classic ASP.

Use correct data types and data sizes in the databaseThis means that if you have a colunn which is a persons name, the data type size only needs to be 40 characters.

There is no need to have a data size any larger than required.

Convert text to htmlBefore storing text in a database, convert it into html.  This will change inputs such as the Javascript <script> to its html equilivant which cannot be executed on a web page.

Filter out any characters that may cause issues. and are not required.

Use parameterized queries

If you use parametized queries for connection to the database you eliminate string concatenation.  You should always use parametized queries rather than constucting the sql.

Check characters particlarly with username / password

If an entry is a username, it normally does not require any other characters other than a to z and 0 to 9 and it only needs to be say, 8 characters long.

Restoring Your mySQL Database From Backup using phpMyAdmin

phpMyAdmin is a program used to manipulate databases remotely through a web interface. A good hosting package will have this included.

  1. Login to phpMyAdmin.
  2. Create a new blank database first.
  3. Click databases, and select the database that you will be importing your data into.
  4. Across the top of the screen will be a row of tabs. Click the Import tab.
  5. On the next screen will be a Location of Text File box, and next to that a button named Browse.
  6. Click Browse. Locate the backup file stored on your computer.
  7. Make sure the SQL radio button is checked.
  8. Click the Go button.

Eventually you will see a success screen.

Backing up a database using phpMyAdmin

This desciption is applicable to all the applications PHP-eSeller, PHP-SecureArea and PHP-KeyCodes.

You should backup your database at regular intervals. You will then be able to restore the database if something goes wrong.

phpMyAdmin is the name of the program that you can use to manipulate databases.  It is usually provided as part of you control panel from your hosting company.

1.  Log into your web server control panel to access phpMyAdmin

2. Select ‘Databases’

Backup 1

3. Now click the name of your database.

Backup 2

4. The next screen will show you all the tables inside your database.  Click the ‘Export’ tab on the top set of tabs.

Backup 3

5. Look at the left box at the top of the Export section. All the tables in the database you selected are in that box.

Backup 4

* If you have other programs that use the database, then choose only those tables that correspond to your install. In the case of PHP-eSeller, they will be the ones with that start with “ipn_”, with PHP-SecureArea they are the ones that start with “sec_” and with PHP-KeyCodes, they are the ones that start with “key_”
* If the database is being used only by the one program, then, leave it as is (or click ‘Select All’ if you changed the selection)
* Ensure that SQL is checked.

6. In the SQL section, tick the following boxes:

* ‘Structure’
* ‘Add DROP TABLE’
* ‘Add AUTO_INCREMENT’ and
* ‘Enclose table and field names with backquotes’

7. In the DATA section, leave the boxes inside this section unticked, but make sure to keep the checkbox next to the “DATA” heading checked.

Backup 4

8.  Tick the ‘Save as file’ option, and leave the template name as is.

Backup 5

9. Now click ‘Go’ and you should be prompted for a file to download. Save the file to your computer.  Depending on the database size, this may take a few moments.

10. You have now backed up your database.   If you wanted, you could download a backup in each of the compression formats. Your choice. For example: None and “zipped

Remember : You have not backed up your files or images.  Only the database itself.

Creating a mySQL Database in cPanel (new style)

Log in to your Cpanel

Log into cPanel and click on the mySQL database link.

Create A New Database

Scroll down to where it says ‘new database’ and enter your choosen database name in the field and click on ‘Create Database’

MySql Account Maintenance Page 

Each step in the process will take you back and forth to the mySQL Maintenace page to verify that you have set up the process correctly and you will be able to see the information you have created.

Here you will see you have added the database.

Click on the Go Back button.

Enter Username and Password

Scroll down to username and password and enter your information.  Click on create user.

Back to MySql Account Maintenance Page 

The username and password have been created for you database.  Click on the Go Back button to return.

Add username to database

Scroll down to add users to your databases.

If there are no users or databases previously created you will only see one in each selection.

Choose the username that you want to add.  Note that the username previously created “myusername” has been shortened to “myusern”  This is the name that you need to use in the config.php file. 

Example: “vonnamia_myusern” not “vonnamia_myusername”

Select the Database you created from the list.

Select the Checkbox “All Privileges”.

Click Add User To Database

MySql Account Maintenance 

This will take you back to the MySql Account Maintenance page for verification, you will see the shortened Username there.

Click on the Go Back button and you will see the contents of your finished Database.

Running the install.php file

At this point you have created the database, created a user and allocated the user to the database.

You can now create the tables using the install.php script supplied with the zip file.

You will need to have the following information available :

database name
database username
database password
database host (usually local host but refer to your host documentation if you are not sure)

When you run the install.php file in your browser you will be presented with boxes as follows :

Create a mySQL database with GoDaddy hosting

This describes creating a database with GoDaddy hosting

First, start by logging into your GoDaddy account:

Click on my hosting account (dropdown under “hosting”)

Click on “manage account” under the correct hosting account domain

Once you’re in the “hosting control center” click on “mysql” from the “databases” dropdown menu

Click the “create database” button

Choose MySQL version 4 or 5, and then enter a description (can be anything), database/user name (must be very unique, or you’ll have to try again), and password (must use at least one capital letter and a number)

(NOTE: you may have to wait 5-10 minutes while the database is setup )

Click the little pencil icon to edit/view database details

Write down or copy the “Host Name:” this will go in the wp-config.php file in place of “localhost”

In your web browser, go to the installation script install.php and fill in the details:

Create a mySQL database using cpanel (old style)

This tutorial will take you step-by-step through the process of creating a database for use with withinweb applications using CPanel.

First, login to your cpanel control panel.

You should see a large number of icons, one of which will be mySQL Database

Click on this and you will be taken to the mySQL Account Maintenance page.  You may also see other database details listed if you have created other database before.

Add User

You need to first add a user name and a password.

Click the Add User button

Note that your host will usually add a prefix to the user name, so this will become something like wptemp_Podz

Create your database

You should now be looking at the screen below. Note that you – the User – are listed in a box at the top, and also below the line too.

Enter the name of the database where database name is listed. Then click Add Db

The database is now created.

Allocate the user to the database

The two pieces of information you just added are now here on this screen: the user name and the database name.

If you have other databases and users, the whole screen may look different, but this small part will look the same.

Look at the two drop-down boxes.

The User box MUST contain the name that you added first above. (Note that for each name you put in, the CPanel has added a prefix.) If you need to, click the drop down to get the name you added to appear in the left hand box. The same applies to the database: – get the name of the database in the box too.

When finished, click the button that says Add User to Db.

This is REALLY important – nothing will work unless you click that button.

You should see this.

At this point you have created the database, created a user and allocated the user to the database.

You can now create the tables using the install.php script supplied with the zip file.

You will need to have the following information available :

database name
database username
database password
database host (usually local host but refer to your host documentation if you are not sure)

When you run the install.php file in your browser you will be presented with boxes as follows :