MySql training

Converting a MySQL Injection Script for Use in Microsoft SQL Server

By Lucas Green

MySQL Server is the most widely used database management system in the world, primarily because it is open source and free. Hence, most databases you may get from outside sources will probably be in the form of a MySQL injection script. This is fine if you use MySQL for your own website databases, but if you use Microsoft SQL Server the script will require a little editing before it will work.

The first thing you'll need to do is remove any comment lines from the script. MySQL comment lines begin with a pound character ("#") and MSSQL comment lines begin with a double dash ("--"), which makes them completely incompatible and will product a syntax error if you try to import a MySQL injection as-is into MSSQL Server. So to get started, open up Query Analyzer if you haven't already (the easiest way to run scripts in MSSQL Server), load up the injection script you are working with, and remove any comment lines (look for the pound symbol). It is easier just to remove them than it is to try and convert them to propery MSSQL syntax, and they are just comment lines anyway so it won't affect anything.

The bulk of your script will most likely be a series of INSERT statements, and these aren't very different in MSSQL as compared to MySQL. However, your script may also include at the beginning a small section that creates the database table where the data will be inserted, and this CREATE TABLE statement is likely to be VERY different in MSSQL, depending on how complicated it is (there could be primary and secondary keys, constraints, even triggers -- the more of these the more the syntax changes from MySQL to MSSQL). Since this is likely to give you the most trouble, it is recommended that you create the database tables manually in Enterprise Manager rather than trying to convert the syntax of the script snippet. Looking at the code, you should be able to easily identify the fields and their types (such as int, varchar, text, etc). Once you have the database table created in Enterprise Manager, delete the snippet of code from the injection script that deals with the creation of the table.

Now all that remains is to convert the INSERT statements to the proper syntax for MSSQL Server. There are a few different steps to accomplish this, but none of them are very complicated. The first difference in syntax between MySQL and MSSQL is that in MySQL, all statements must end with a semicolon (";"). In MSSQL, this is a syntax error. The easiest way to remove these semicolons is to do a search and replace, and since the INSERT statements should be passing a series of values for each record of data, each line of the MySQL script will most likely end with a paranthesis and semicolon (");"). So, do a search and replace and replace all instances of ");" with just the parenthesis ")".

Another difference that you will have to correct for is that your MySQL injection script will most likely use an acute accent / reverse apostrophe (ANSI character 180) around the table name on each line. In MSSQL Server, you can encapsulate an object's name (such as a table's) with either square brackets ("[" and "]") or nothing at all. However, you probably don't want to do a blanket search-and-replace of the reverse apostrophe character, because that character might be used in the data of each record (especially if the data contains text, such as an article body). The easiest way to correct for this difference in syntax, then, is to do another search and replace, and replace all instances of the reverse apostrophe AND the table name, for example "`articles`" with just the table name "articles".

Finally, there will also be numerous occurrences of apostrophes throughout the text fields of the data, and the apostrophe character is used to encapsulate strings in the script. In MySQL, the way to escape an apostrophe so that the script knows it is part of the text and not the end of the string, is to use a backslash followed by the apostrophe ("\'"). In a MSSQL Server script, the proper way to escape an apostrophe is to use a double apostrophe ("''"). So, one more search and replace is called for -- this time, replace all instances of [\'] with [''] (double apostrophe, NOT an actual quotation mark).

Once these steps are all complete, you are ready to run the script! There shouldn't be any other syntax changes you'll have to make, but don't worry if there are because when you execute the injection script it will tell you if there are any errors. If everything was corrected properly and there are no errors, you should get a series of "1 row(s) affected" responses -- one for each INSERT statement in the script. If you want to verify that the proper number of records are in the database table, you can execute a "select count(*) from tablename" statement to count the rows of the table -- it should match the number of lines in the injection script, give or take a few for blank lines, etc.

That's it! Your options are now increased tremendously, because now you can use either MySQL or MSSQL injection scripts to import acquired databases into your database system. If you use MySQL as your dbms, you can do this process in reverse to convert a MSSQL injection script into a MySQL one. Either way, you now can import data using an injection script from either of the two most popular database management systems in the world. Now, where to obtain such databases or injection scripts is another question entirely, and beyond the scope of this article. Suffice it to say that there are numerous sources on the internet where you can purchase or acquire databases -- a good one is www.WebContents.org. I think you will find that not only is it much easier to acquire content databases for your users than it is to build them from scratch, but it also is an easy way to add a lot of new, fresh content for your users with a minimal amount of time and effort. Using this method, you can get databases of articles, jokes, quotes, recipes, etc, and put them right on your website or any other database-integrated application, with very little work. Good luck!

Developing A Login System With PHP And MySQL

By John L

Most interactive websites nowadays would require a
user to log in into the website’s system in order to provide a
customized experience for the user. Once the user has logged in, the
website will be able to provide a presentation that is tailored to the
user’s preferences.

A basic login system typically contains 3 components:

1. The component that allows a user to register his preferred login id and password

2. The component that allows the system to verify and authenticate the user when he subsequently logs in

3. The component that sends the user’s password to his registered email address if the user forgets his password

Such a system can be easily created using PHP and MySQL.

================================================================

Component 1 – Registration

Component 1 is typically implemented using a simple HTML form that contains 3 fields and 2 buttons:

1. A preferred login id field

2. A preferred password field

3. A valid email address field

4. A Submit button

5. A Reset button

Assume
that such a form is coded into a file named register.html. The
following HTML code excerpt is a typical example. When the user has
filled in all the fields, the register.php page is called when the user
clicks on the Submit button.

[form name="register" method="post" action="register.php"]

[input name="login id" type="text" value="loginid" size="20"/][br]

[input name="password" type="text" value="password" size="20"/][br]

[input name="email" type="text" value="email" size="50"/][br]

[input type="submit" name="submit" value="submit"/]

[input type="reset" name="reset" value="reset"/]
[/form]

The
following code excerpt can be used as part of register.php to process
the registration. It connects to the MySQL database and inserts a line
of data into the table used to store the registration information.

@mysql_connect("localhost", "mysql_login", "mysql_pwd") or die("Cannot connect to DB!");
@mysql_select_db("tbl_login") or die("Cannot select DB!");
$sql="INSERT INTO login_tbl (loginid, password and email) VALUES (".$loginid.”,”.$password.”,”.$email.”)”;
$r = mysql_query($sql);
if(!$r) {

$err=mysql_error();

print $err;

exit();
}

The
code excerpt assumes that the MySQL table that is used to store the
registration data is named tbl_login and contains 3 fields – the
loginid, password and email fields. The values of the $loginid,
$password and $email variables are passed in from the form in
register.html using the post method.

================================================================

Component 2 – Verification and Authentication

A
registered user will want to log into the system to access the
functionality provided by the website. The user will have to provide
his login id and password for the system to verify and authenticate.

This is typically done through a simple HTML form. This HTML form typically contains 2 fields and 2 buttons:

1. A login id field

2. A password field

3. A Submit button

4. A Reset button

Assume
that such a form is coded into a file named authenticate.html. The
following HTML code excerpt is a typical example. When the user has
filled in all the fields, the authenticate.php page is called when the
user clicks on the Submit button.

[form name="authenticate" method="post" action="authenticate.php"]

[input name="login id" type="text" value="loginid" size="20"/][br]

[input name="password" type="text" value="password" size="20"/][br]

[input type="submit" name="submit" value="submit"/]

[input type="reset" name="reset" value="reset"/]
[/form]

The
following code excerpt can be used as part of authenticate.php to
process the login request. It connects to the MySQL database and
queries the table used to store the registration information.

@mysql_connect("localhost", "mysql_login", "mysql_pwd") or die("Cannot connect to DB!");
@mysql_select_db("tbl_login") or die("Cannot select DB!");
$sql="SELECT loginid FROM login_tbl WHERE loginid=’".$loginid.”’ and password=’”.$password.”’”;
$r = mysql_query($sql);
if(!$r) {

$err=mysql_error();

print $err;

exit();
}
if(mysql_affected_rows()==0){

print "no such login in the system. please try again.";

exit();
}
else{

print "successfully logged into system.";

//proceed to perform website’s functionality – e.g. present information to the user
}

As
in component 1, the code excerpt assumes that the MySQL table that is
used to store the registration data is named tbl_login and contains 3
fields – the loginid, password and email fields. The values of the
$loginid and $password variables are passed in from the form in
authenticate.html using the post method.

================================================================

Component 3 – Forgot Password

A
registered user may forget his password to log into the website’s
system. In this case, the user will need to supply his loginid for the
system to retrieve his password and send the password to the user’s
registered email address.

This is typically done through a simple HTML form. This HTML form typically contains 1 field and 2 buttons:

1. A login id field

2. A Submit button

3. A Reset button

Assume
that such a form is coded into a file named forgot.html. The following
HTML code excerpt is a typical example. When the user has filled in all
the fields, the forgot.php page is called when the user clicks on the
Submit button.

[form name="forgot" method="post" action="forgot.php"]

[input name="login id" type="text" value="loginid" size="20"/][br]

[input type="submit" name="submit" value="submit"/]

[input type="reset" name="reset" value="reset"/]
[/form]

The
following code excerpt can be used as part of forgot.php to process the
login request. It connects to the MySQL database and queries the table
used to store the registration information.

@mysql_connect("localhost", "mysql_login", "mysql_pwd") or die("Cannot connect to DB!");
@mysql_select_db("tbl_login") or die("Cannot select DB!");
$sql="SELECT password, email FROM login_tbl WHERE loginid=’".$loginid.”’”;
$r = mysql_query($sql);
if(!$r) {

$err=mysql_error();

print $err;

exit();
}
if(mysql_affected_rows()==0){

print "no such login in the system. please try again.";

exit();
}
else {

$row=mysql_fetch_array($r);

$password=$row["password"];

$email=$row["email"];

$subject="your password";

$header="from:you@yourdomain.com";

$content="your password is ".$password;

mail($email, $subject, $row, $header);

print "An email containing the password has been sent to you";

}

As
in component 1, the code excerpt assumes that the MySQL table that is
used to store the registration data is named tbl_login and contains 3
fields – the loginid, password and email fields. The value of the
$loginid variable is passed from the form in forgot.html using the post
method.

================================================================

Conclusion

The
above example is to illustrate how a very basic login system can be
implemented. The example can be enhanced to include password encryption
and additional functionality – e.g. to allow users to edit their login
information.

Server Side Programming Languages

By: dave

Server-side programming languages are scripts that are executed on the server, and are then translated into HyperText Markup Language (HTML) which can be viewed by all web browsers. The two most popular server-side scripting languages are PHP: Hypertext Processor and Active Server Pages (ASP). Additionally, there are numerous other languages like AJAX and Coldfusion.

PHP can run on both Unix and Windows servers, which makes it more accessible than its Windows counterpart, Active Server Pages (ASP). Most full-service web design firms will have at least one PHP guru.
PHP uses are widespread, and can include any kind of server functionality that takes user's input and displays or manipulates the input. Some pertinent examples of such work are message boards, auction sites, shopping carts, and more. There are numerous free (open-source) scripts out there for PHP newbies to use. This synopsis is meant to serve only as a gateway to other works; although the main goal is to give a reader enough information so they can make educated decisions about what their web developer should do. For those looking to get into PHP, there are many free tutorials and primers out there: http://www.4webhelp.net/tutorials/php/basics.php is a pertinent example.
PHP generally uses the mySQL database system. MySQL is a server-side system that is included on many Unix, and some Windows servers.

On the other hand, Active Server Pages runs - for the most part - solely on Windows servers. This can cause some problems. Windows hosting or private servers generally cost more than Unix servers, making it less accessible than PHP. Like PHP, ASP can do just about anything. There are considerably fewer open-source scripts written in ASP, another testament to its inaccessibility.
For those interested in ASP, here's a great free tutorial: http://www.w3schools.com/asp/default.asp.

ASP can use many different database systems. Many users prefer Microsoft Access. Access, unlike MySQL, offers a what-you-see-is-what-you-get (WYSIWYG) editor as part of Microsoft's Office suite. In fact, you may already have a copy of Microsoft Access on your computer and not even know it. Its uses aren't limited to databasing, it's also used as a basic spreadsheet application for those who need a more programmer-friendly environment than Excel. ASP can also work well with MSSQL or MySQL.

A third programming language with burgeoning popularity is Asynchronous Javascript and XML. AJAX, as it's commonly referred to, creates interactive web programs just like its cousins ASP and PHP. AJAX uses XHTML and CSS, along with the Javascript Document-Object Model to create interactive pages designed for speed and overall usability. Although AJAX hasn't gained the acclaim of PHP and ASP, its future is certainly bright.
AJAX Basics - http://dhtmlnirvana.com/ajax/ajax_tutorial/

It's difficult to say which of the three programming languages, or the numerous others for that matter, is the best. There will always be disputes, and no standard is set. With the varying interpretations of what a programming language should be, predilections to PHP or ASP arise. PHP is certainly more widely used, but isn't necessarily the best. When a site is being created to be interactive, a professional can give an educated opinion on which technology should be used.