Erm's I.T. Girl – Zelna Ellis

Don't fear when Zel is near…

VB.NET – Catch error messages from Process

In previous posts, how to run MySQL scripts and add local users and groups with net command in VB.NET, it did not catch any errors from the child processes running.

In this post, you will see how easy it is to catch those error messages.

Download code (PDF) here.

In this example, it will display message "Process Completed" or the "Error message" in a label on the GUI.

Process

Process

Here is the code snippet:
Private Sub RunTheCommand(ByVal TheCommand As String, ByVal TheArguments As String)
     Try
          Dim proc As New Process
          Dim err As String = ""
          'Clear any messages in the GUI.
          lblError.Text = ""

          proc.StartInfo.FileName = TheCommand
          proc.StartInfo.Arguments = TheArguments

          'StartInfo.UseShellExecute must be False when StartInfo.RedirectStandardError = True.
          'Otherwise when reading the stream from StandardError will throw an exception.
          proc.StartInfo.UseShellExecute = False
          proc.StartInfo.RedirectStandardError = True
          'Don’t show command prompt window.
          proc.StartInfo.CreateNoWindow = True
          'Start process
          proc.Start()
          'Read the error stream (synchronously) first to the end before
          'waiting for the child process to exit – to avoid a deadlock.
          err = proc.StandardError.ReadToEnd
          proc.WaitForExit()
          proc.Close()

          'Display errors on the GUI if any
          If err = "" Then
               lblError.Text = "Process Completed"
          Else
               lblError.Text = err
          End If

     Catch ex As Exception
          MessageBox.Show(Err.Description, "Process Error", _
               MessageBoxButtons.OK, MessageBoxIcon.Error)
     End Try
End Sub

Happy Coding πŸ™‚

17 October 2009 Posted by | .NET & MySQL | , , , , | Leave a comment

VB.NET Run MySQL Script

To run a MySQL Script in VB.NET has one trick: The command window must stay open while the script is running.

Here is an example of the arguments string to pass to the cmd.exe:
"/K ""mysql.exe"" " & "-h hostname -u username -ppassword < filename.sql"

Explanation of some of the arguments string:
/K = Carries out the command specified by the string but doesn’t terminate – cmd window remains open.
Then the MySQL values:
hostname = MySQL Server hostname
username = MySQL username
password = MySQL user’s password
filename = MYSQL script – must be full path.

Note:
I’ve encountered errors when one doesn’t specify the time in process.WaitForExit().

Code Snippet:

Private Sub RunMySQLScript(ByVal TheArguments As String)
     Try
          Dim proc As New Process
          proc.StartInfo.FileName = "cmd.exe"
          proc.StartInfo.UseShellExecute = False
          'Don’t show command prompt window.
          proc.StartInfo.CreateNoWindow = True
          proc.StartInfo.Arguments = TheArguments
          proc.Start()
          'Time in milliseconds, can change value to wait.
          proc.WaitForExit(1000)
          proc.Close()
     Catch ex As Exception
          MessageBox.Show(Err.Description, "Run MySQL Scripts", _
          MessageBoxButtons.OK, MessageBoxIcon.Error)
     End Try
End Sub

10 October 2009 Posted by | .NET & MySQL | , , , , | 1 Comment

VB.NET Add Local User and Group Accounts with Net Command

It can be quite a mission if you need to add a local user and/or group without AD (Active Directory) in VB.NET. There are other ways, e.g. Scripting etc., but this is really easy. The downfall here is that you won’t see any error messages but you can always use WMI to see if a group and/or user exists. The normal rules for the net.exe command count and you can use the parameters you need.

proc.StartInfo.Arguments = "enter the parameters for the command here"
If you do know of a more efficient way to do it, please share it with us.

Note: Usually one will get the username, password, group, etc. from the GUI’s textboxes. Left it out in this example to simplify the code.

Download code (PDF) here.

Imports System
Imports System.Diagnostics

Public Class CheckUser02

     Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
          Try
               Dim proc As New Process
               proc.StartInfo.FileName = "net.exe"
               proc.StartInfo.UseShellExecute = False
               proc.StartInfo.CreateNoWindow = True 'Don’t show command prompt window.

               'Add localgroup named "test" with "The Test Group" as a comment(Description of Group)
               proc.StartInfo.Arguments = "localgroup test /ADD /COMMENT:Test-Group"
               proc.Start()
               proc.Close()
               'Add local user names "test1" where the user password = "12345"
               proc.StartInfo.Arguments = "user test1 12345 /ADD /FULLNAME:TestUser1"
               proc.Start()
               proc.Close()
               'Add local user "test1" to localgroup "test1"
               proc.StartInfo.Arguments = "localgroup test test1 /ADD"
               proc.Start()
               proc.Close()

          Catch ex As Exception
               MsgBox(Err.Description)
          End Try
     End Sub
End Class

29 September 2009 Posted by | .NET & MySQL | , , , , , , , , | 3 Comments

VB.NET & MySQL: Check Status, Start or Stop Windows Service

Well the title says it all. In this example MySQL Server is used to check whether the service exists. If it is disabled it will set the StartupStatus to Manual. Then it will check whether or not the Service is running, and start it if necessary.
You can just replace it with the Windows Service you need to manipulate.

You can download the code in PDF format here.

Note: tsStatus is a ToolStripStatusLabel that is on the form. Tested on Windows XP SP3

Imports System
‘Remember to add references to the following namespaces:
Imports System.ServiceProcess
Imports System.Management

Public Class MySQLCheck

     Private Sub MySQLCheck_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          Try
               Dim mo As New Management.ManagementObject("Win32_Service.Name=’MySQL’")
               tsStatus.Text = "Busy…"
               ‘Check if MySQL Service is installed. If not it will close the form.
               Try
                    mo.Get()
               Catch ex As Exception
                    MessageBox.Show("Need to install MySQL Server", "MySQL Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Me.Close()
               End Try

               ‘Check if the StartMode = Disabled. If so it will change it to Manual
               If mo("StartMode").ToString = "Disabled" Then
               Dim inParams, outParams As ManagementBaseObject
               Dim result As Integer

               ‘Get an input parameters object for this method
               inParams = mo.GetMethodParameters("ChangeStartMode")
                ‘Set the StartMode to ‘Manual’
               inParams("StartMode") = "Manual"
               outParams = mo.InvokeMethod("ChangeStartMode", inParams, Nothing)
               ‘Check for errors 0 means the request has been accepted.
               result = Convert.ToInt32(outParams("returnValue"))

               ‘Handle errors if any
               If result <> 0 Then
                    Dim myErrMsg As String = ""
                    Select Case result
                         Case 1
                              myErrMsg = "The request is not supported."
                         Case 2
                              myErrMsg = "The user did not have the necessary access."
                         Case 3
                              myErrMsg = "The service cannot be stopped because other services that are running are dependent on it."
                         Case 4
                              myErrMsg = "The requested control code is not valid, or it is unacceptable to the service."
                         Case 5
                              myErrMsg = "The requested control code cannot be sent to the service because the state of the service (Win32_BaseService State property) is equal to 0, 1, or 2."
                         Case 6
                              myErrMsg = "The service has not been started."
                         Case 7
                              myErrMsg = "The service did not respond to the start request in a timely fashion."
                         Case 8
                              myErrMsg = "Interactive process."
                         Case 9
                              myErrMsg = "The directory path to the service executable file was not found."
                         Case 10
                              myErrMsg = "The service is already running."
                         Case 11
                              myErrMsg = "The database to add a new service is locked."
                         Case 12
                              myErrMsg = "A dependency on which this service relies has been removed from the system."
                         Case 13
                              myErrMsg = "The service failed to find the service needed from a dependent service."
                         Case 14
                              myErrMsg = "The service has been disabled from the system."
                         Case 15
                              myErrMsg = " The service does not have the correct authentication to run on the system."
                         Case 16
                              myErrMsg = "This service is being removed from the system."
                         Case 17
                              myErrMsg = "There is no execution thread for the service."
                         Case 18
                              myErrMsg = "There are circular dependencies when starting the service."
                         Case 19
                              myErrMsg = "There is a service running under the same name."
                         Case 20
                              myErrMsg = "There are invalid characters in the name of the service."
                         Case 21
                              myErrMsg = "Invalid parameters have been passed to the service."
                         Case 22
                              myErrMsg = "The account which this service is to run under is either invalid or lacks the permissions to run the service."
                         Case 23
                              myErrMsg = "The service exists in the database of services available from the system."
                         Case 24
                              myErrMsg = "The service is currently paused in the system."

                    End Select
                    Throw New Exception("ChangeStartMode method error code " & result & ControlChars.NewLine & myErrMsg)
               End If
          End If

          ‘Check status of MySQL Server
          ‘If the service is running all is fine
          ‘Else it will wait for the Server to run, or attempt to start the Server
          ‘The status will be updated in the ToolStripStatusLabel tsStatus

          ‘ServiceControllerStatus Meanings
          ‘1 = Stopped – The Service is not running.
          ‘2 = StartPending – The Service is starting.
          ‘3 = StopPending – The Service is stopping.
          ‘4 = Running – The Service is running.
          ‘5 = ContinuePending – The Service continue is pending.
          ‘6 = PausePending – The Service pause is pending.
          ‘7 = Paused – The service is paused.
          Dim sc As New ServiceController("MySQL")
          Select Case sc.Status
               Case 1
                    tsStatus.Text = "MySQL Server is not running, please wait…"
                    sc.Start()
                    tsStatus.Text = "Starting MySQL Server, please wait…"
                    sc.WaitForStatus(ServiceControllerStatus.Running)
                    tsStatus.Text = "Ready"
               Case 2
                    tsStatus.Text = "MySQL Server is starting, please wait…"
                    sc.WaitForStatus(ServiceControllerStatus.Running)
                    tsStatus.Text = "Ready"
               Case 3
                    tsStatus.Text = "MySQL Server is stopping, please wait…"
                    sc.WaitForStatus(ServiceControllerStatus.Stopped)
                    tsStatus.Text = "Starting MySQL Server, please wait…"
                    sc.Start()
                    sc.WaitForStatus(ServiceControllerStatus.Running)
                    tsStatus.Text = "Ready"
               Case 4
                    tsStatus.Text = "Ready"
               Case 5, 6, 7
                    tsStatus.Text = "MySQL Server is stopping, please wait…"
                    sc.Stop()
                    sc.WaitForStatus(ServiceControllerStatus.Stopped)
                    tsStatus.Text = "Starting MySQL Server, please wait…"
                    sc.Start()
                    sc.WaitForStatus(ServiceControllerStatus.Running)
                    tsStatus.Text = "Ready"
          End Select
          Catch ex As Exception
               MessageBox.Show(Err.Description, "MySQL Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
               Me.Close()
          End Try
     End Sub
End Class

28 September 2009 Posted by | .NET & MySQL | , , , , , , , , , , , , , , , , | 5 Comments

MySQL & SQL_Mode Issues

Sometimes I wonder why I’ve started to do things in MySQL, whilst Oracle PL/SQL give me so little trouble in comparison with MySQL.

O.K. SQL_Mode is not something to throw out of the back-door but I really hope that certain issues will get sorted out in the next version of MySQL. Will have to wait and see?

O.K. the next problem I’ve been running into is to insert the current username into a column in a table, and that is all got to with SQL_mode issues. I like the mode stuff coz it give us better data integrity….

Example Code:
#Loose up on the restriction!

SET @OLD_SQL_MODE=@@SQL_MODE;
DROP SCHEMA IF EXISTS TESTIT;
CREATE SCHEMA TESTIT;
USE TESTIT;
DROP TABLE IF EXISTS customer;
CREATE TABLE customer(
custID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(50) NOT NULL,
creationDATE DATETIME,
lastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
myUserName VARCHAR(50),
lastUserName VARCHAR(50),
PRIMARY KEY (custID)
);
ALTER TABLE customer AUTO_INCREMENT = 1001;
DROP TRIGGER IF EXISTS testCustUpd1;
DELIMITER |
CREATE TRIGGER testCustUpd1 BEFORE INSERT ON customer
FOR EACH ROW BEGIN
SET NEW.creationDate = NOW();
SET NEW.myUserName = USER();
SET NEW.lastUserName = USER();
END;
|
DELIMITER ;
DROP TRIGGER IF EXISTS testCustUpd2;
CREATE TRIGGER testCustUpd2 BEFORE UPDATE ON customer FOR EACH ROW SET NEW.lastUserName = USER();
#LOOSEN STRICT SQL;
SET SESSION SQL_MODE='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
INSERT INTO customer (firstName) VALUES ('Angel');
INSERT INTO customer (firstName) VALUES ('Bennie');
SELECT * FROM customer;
UPDATE customer
SET firstName='Christo'
WHERE firstName='Bennie';
SELECT * FROM customer;
SET SESSION SQL_MODE=@OLD_SQL_MODE;

CONCLUSION
This will still give you a warning but the tables will be updated. Hope we can see these issues resolved in future releases of MySQL.

9 April 2009 Posted by | .NET & MySQL | , , , | Leave a comment

MySQL Date Issues

Oh what a week…
MySQL have a restriction towards dates.
I have 2 columns creationDate and lastUpdate in some tables but:

  • There can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.
  • You cannot use the function NOW() as a default value for a DATETIME variable.

I wanted both values in my table what now?

SOLUTION:
Set the variable type for creationDate to DATETIME variable type and lastUpdate to TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
Create a trigger to update the field creationDate.

CODE EXAMPLE:

DROP SCHEMA IF EXISTS TESTIT;
CREATE SCHEMA TESTIT;

USE TESTIT;

DROP TABLE IF EXISTS customer;

CREATE TABLE customer(
custID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(50) NOT NULL,
creationDATE DATETIME,
lastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (custID)
);


ALTER TABLE customer AUTO_INCREMENT = 1001;

CREATE TRIGGER setMyCreationDate BEFORE INSERT ON customer FOR
EACH ROW SET NEW.creationDate = NOW();

INSERT INTO customer (firstName)
VALUES ('Angel');
INSERT INTO customer (firstName)
VALUES ('Bennie');

SELECT * FROM customer;

UPDATE customer
SET firstName='Christo'
WHERE firstName='Bennie';

SELECT * FROM customer;

Click to view full picture in new tab page:
Use creation date and last update together

CONCLUSION:
Personally I would rather see that MySQL can support the function NOW() as a default value for date variables.

8 April 2009 Posted by | .NET & MySQL | , , , , | Leave a comment

.NET & MySQL – Part 6

MySQL Connection Strings

Software used:

Read more about connection strings at Connectionstrings

One way is to use the Application Configuration File.

  • Right click on the Solution Project Name and select Properties.
  • Select Settings.
    Name: Give it a name
    Type: Choose (Connection String)
    Scope: Application
    Value: You can manually type the values or click on the eclipses to open the dialog box to fill in it. E.g.: server=localhost;user id=jdoe;database=test;encrypt=true;

Application Config
Note:
Only use encrypt=true if SSL is enabled in the MySQL Server and the users SSL options are set.

I recommend that you do not store the password, through a log-in screen you can prompt the user for the password and construct the connection string together with the values of the app.config to connect to the database.

What is also important is that the file must be stored at a secure folder, which is only accessable by the PC Admin.
Typically this would be: C:\Document and Settings\Your User Name\Local Settings\Appication Data\Your Application Name\

Some more options:

  • Write it to the Windows’ registry
  • Write it to a custom file (e.g. XML File)

More suggestions:

  • Encryption/Decryption
    There are various ways you can encrypt the XML File (App.Config file is also in XML Format). For more information read the MSDN Documentation.
  • Hashing
    Passwords are Encrypted and never Decrypted, hence hashing. Once a password is Encrypted and stored in the database, you will never decrypt the retrieved password from the database. If you want to check if the password that the user entered matches, you encrypt the password that the user entered and compare it with the encrypted password stored in the database.

Conclusion
This post has briefly discussed various options regarding security, specifically looking at connection strings.
Besides the connection strings it is important to secure the MySQL Server as well as the Operating System.
I truly believe that no system is "Bullet Proof!" You can only make it more difficult…


Previous Posts:
.NET & MySQL Part 1 A list of software required as well as optional software that can be used.
.NET & MySQL Part 2 Install MySQL Server
.NET & MySQL Part 3 Install PHP on Windows XP IIS Server
.NET & MySQL Part 4 Setup MySQL Connection String for a Windows application using VB.NET.
.NET & MySQL Part 5 MySQL and OpenSSL.

1 April 2009 Posted by | .NET & MySQL | , , , | Leave a comment

.NET & MySQL Part 5

MySQL and OpenSSL

Software used

In this guide:

  • Generate SSL Certificates
      Create CA certificate
      Create server certificate
      Create client certificate
  • Configure MySQL Server
  • Setting User SSL Properties
  • The MySQL manual is quite clear how to create the SSL certificates, but vague how to implement it on Windows.
    See Chapter 5.5.7 Using SSL for Secure Connections.
    See Chapter 5.5.7.4. Setting Up SSL Certificates for MySQL

    Generate Certificates

    Create CA certificate

  • openssl genrsa 2048 > ca-key.pem

    D:\newcerts>openssl genrsa 2048 > ca-key.pem
    Loading ‘screen’ into random state – done
    Generating RSA private key, 2048 bit long modulus
    ……………………………………………………………………..
    ……………….+++
    ……..+++
    e is 65537 (0x10001)

  • openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

    D:\newcerts>openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.p
    em
    Loading ‘screen’ into random state – done
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter ‘.’, the field will be left blank.
    —–
    Country Name (2 letter code) [AU]:ZA
    State or Province Name (full name) [Some-State]:Mpumalanga
    Locality Name (eg, city) []:Ermelo
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:Test
    Organizational Unit Name (eg, section) []:Test
    Common Name (eg, YOUR name) []:Test
    Email Address []:test@mail.com

  • Create server certificate

  • openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

    D:\newcerts>openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pe
    m > server-req.pem
    Loading ‘screen’ into random state – done
    Generating a 2048 bit RSA private key
    ……………………………………………………………………..
    ………………………….+++
    ……………………………..+++
    writing new private key to ‘server-key.pem’
    —–
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter ‘.’, the field will be left blank.
    —–
    Country Name (2 letter code) [AU]:ZA
    State or Province Name (full name) [Some-State]:Mpumalanga
    Locality Name (eg, city) []:Ermelo
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:TestCo
    Organizational Unit Name (eg, section) []:TestCo
    Common Name (eg, YOUR name) []:TestCo
    Email Address []:testco@mail.com

    Please enter the following ‘extra’ attributes
    to be sent with your certificate request
    A challenge password []: xxxxxxxxxxxx
    An optional company name []: xxxxxxxxxx

  • openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

    D:\newcerts>openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAk
    ey ca-key.pem -set_serial 01 > server-cert.pem
    Loading ‘screen’ into random state – done
    Signature ok
    subject=/C=ZA/ST=Ermelo/L=Ermelo/O=TestCo/OU=TestCo/CN=TestCo/emailAddress=testc
    o@mail.com
    Getting CA Private Key

  • Create client certificate

  • openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

    D:\newcerts>openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pe
    m > client-req.pem
    Loading ‘screen’ into random state – done
    Generating a 2048 bit RSA private key
    ……………….+++
    …+++
    writing new private key to ‘client-key.pem’
    —–
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter ‘.’, the field will be left blank.
    —–
    Country Name (2 letter code) [AU]:ZA
    State or Province Name (full name) [Some-State]:Mpumalanga
    Locality Name (eg, city) []:Ermelo
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:TestClient
    Organizational Unit Name (eg, section) []:TestClient
    Common Name (eg, YOUR name) []:TestClient
    Email Address []:testclient@mail.com

    Please enter the following ‘extra’ attributes
    to be sent with your certificate request
    A challenge password []: xxxxxxxxxxxxxxxx
    An optional company name []: xxxxxxxxxxxxxx

  • openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

    D:\newcerts>openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAk
    ey ca-key.pem -set_serial 01 > client-cert.pem
    Loading ‘screen’ into random state – done
    Signature ok
    subject=/C=ZA/ST=Mpumalanga/L=Ermelo/O=TestClient/OU=TestClient/CN=TestClient/em
    ailAddress=testclient@mail.com
    Getting CA Private Key

Configure MySQL Server

Open the cmd propt and execure the following commands:
net start mysql
mysql -u root -p
enter the password
SHOW VARIABLES LIKE ‘have_ssl’;

mysql> SHOW VARIABLES LIKE ‘have_ssl’;

| Variable_name | Value    |

| have_ssl      | DISABLED |

1 row in set (0.06 sec)

This means MySQL supports SSL but it is not installed.

type exit and press Enter.
mysql> exit
Bye

Stop MySQL server.
D:\>net stop mysql
The MySQL service is stopping.
The MySQL service was stopped successfully.

Take Note:
My PC’s root drive is D:\, usually it is C:\. Change it according to your needs.
From the directory you have created the certificates (See MySQL Manual Chapter 5.5.7.4. Setting Up SSL Certificates for MySQL) copy the following files to D:\Program Files\MySQL\MySQL Server 5.1\ssl\
Note: You can use any directory you want to.

  • ca-cert.pem
  • client-cert.pem
  • client-key.pem
  • server-cert.pem
  • server-key.pem

Open the file D:\Program Files\MySQL\MySQL Server 5.1\my.ini in your favourite text editor.
under the [client] add the following:
ssl-ca="D:/Program Files/MySQL/MySQL Server 5.1/ssl/ca-cert.pem"
ssl-cert="D:/Program Files/MySQL/MySQL Server 5.1/ssl/client-cert.pem"
ssl-key="D:/Program Files/MySQL/MySQL Server 5.1/ssl/client-key.pem"
under the [mysqld] add the following:

  1. Add SSL Support

ssl-ca="D:/Program Files/MySQL/MySQL Server 5.1/ssl/ca-cert.pem"
ssl-cert="D:/Program Files/MySQL/MySQL Server 5.1/ssl/server-cert.pem"
ssl-key="D:/Program Files/MySQL/MySQL Server 5.1/ssl/server-key.pem"

Save and close the file

Start MySQL Server

D:\>net start mysql
The MySQL service is starting…
The MySQL service was started successfully.

Log into MySQL monitor

D:\>mysql -u root -p
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.32-community MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

Check if SSL is active

mysql> SHOW VARIABLES LIKE ‘have_ssl’;

| Variable_name | Value |

| have_ssl      | YES   |

1 row in set (0.02 sec)

Check determine whether the current connection with the server uses SSL by checking the value of the Ssl_cipher

mysql> SHOW STATUS LIKE ‘Ssl_cipher’;

| Variable_name | Value              |

| Ssl_cipher    | DHE-RSA-AES256-SHA |

Setting User SSL Properties

Refer to MySQL Manual Chapter 12.5.1.3. GRANT Syntax
The SSL Options will be sent after REQUIRE clause.
The options are

  • SSL Tells the server to allow only SSL-encrypted connections for the account.
  • X509 Means that the client should have a valid certificate, but we do not care about the exact certificate, issuer or subject.
  • CIPHER ‘cipher’ Is needed to ensure strong ciphers and keylengths will be used.
  • ISSUER ‘issuer’ Means the client must present a valid X509 certificate issued by issuer "issuer".
  • SUBJECT ‘subject’ Means the client must present a valid X509 certificate with the subject "subject" on it.

Examples:
GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE SSL;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE X509;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE CIPHER ‘DHE-RSA-AES256-SHA’;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE ISSUER ‘/C=ZA/ST=Ermelo/L=Ermelo/O=Test/OU=Test/CN=Test/emailAddress=test@mail.com’;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE SUBJECT ‘/C=ZA/ST=Mpumalanga/L=Ermelo/O=TestClient/OU=TestClient/CN=TestClient/emailAddress=testclient@mail.com’;

GRANT ALL PRIVILEGES ON test.* TO ‘myuser’@’localhost’
IDENTIFIED BY ‘mypassword’
REQUIRE SUBJECT ‘/C=ZA/ST=Mpumalanga/L=Ermelo/O=TestClient/OU=TestClient/CN=TestClient/emailAddress=testclient@mail.com’
AND ISSUER ‘/C=ZA/ST=Ermelo/L=Ermelo/O=Test/OU=Test/CN=Test/emailAddress=test@mail.com’
AND CIPHER ‘DHE-RSA-AES256-SHA’;

Happy Coding!

In the Next Post we will return to VB.NET and the Connection Strings.

Previous Posts:
.NET & MySQL Part 1 A list of software required as well as optional software that can be used.
.NET & MySQL Part 2 Install MySQL Server
.NET & MySQL Part 3 Install PHP on Windows XP IIS Server
.NET & MySQL Part 4 Setup MySQL Connection String for a Windows application using VB.NET.

Version 1.0

31 March 2009 Posted by | .NET & MySQL | , , | Leave a comment

.NET & MySQL Part 4

Setup MySQL Connection String for a Windows application using VB.NET

Software used in this post:

In Visual Studio

  • Create a Windows Form Application named TestConn.
    frmTest only has two buttons: btnTest and btnExit.
    Fig. 1
  • Add Reference
    From the menu select Project | Add Reference
    Scroll down, select MySQL.Data, and click OK.
    Fig. 2
  • The Program Code

    Imports MySql.Data.MySqlClient

    Public Class frmTest
      Dim MySQLConnectionString As String
      Dim MyADOConnection As MySqlConnection

     Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click
      Try
        MySQLConnectionString =     “Server=hostname;” & _
        “Database=mydbschema;” & _
        “Uid=myusername;” & _
        “Pwd=mypassword;” & _
        “Connect Timeout=30;”

        MyADOConnection = New MySqlConnection()
        MyADOConnection.ConnectionString = MySQLConnectionString
        MyADOConnection.Open()
        MessageBox.Show(“Connection Opened Successfully!”, “Test Connection”)
      Catch ex As Exception
        MessageBox.Show(“Error Connecting to Database: ” & Err.Description, “Test Connection”))
      End Try
     End Sub

     Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
      Try
        MyADOConnection.Close()
        MyADOConnection.Dispose()
      Catch ex As Exception
        ‘Do Nothing
      End Try
      Application.Exit()
     End Sub
    End Class

  • Run the program
    Click the Test button. The following message should display if the connection was successful:
    Fig. 3
    Click the Exit button

Conclusion:
Read here more about the connection strings.

In this example the connection string was hardcoded (embedded). This is not recommended!

    Why?

  1. A malicious Connection String injection attack can occur. The injection attacker can completely destroy the database or retrieve important secure information.
  2. If any of the Connection String parameters change, the entire applications needs to be recompiled and redeployed again to all the computers.

πŸ™‚ Happy Coding

In the next post .NET & MySQL Part 5 we will have a look how to enable SSL for MySQL Server.

Previous Posts:
.NET & MySQL Part 1 A list of software required as well as optional software that can be used.
.NET & MySQL Part 2 Install MySQL Server
.NET & MySQL Part 3 Install PHP on Windows XP IIS Server

Version 1.1

26 March 2009 Posted by | .NET & MySQL | , , , | Leave a comment

.NET & MySQL Part 3

Install PHP on Windows XP IIS Server

Download guide (PDF)

Prerequisites: IIS (Internet Information Services available on XP CD)
Version: 5.2.9
File Name: php-5.2.9-1-win32-installer.msi
Download Location: http://www.php.net/get/php-5.2.9-1-win32-installer.msi/from/a/mirror
For the latest version check it out at http://www.php.net/downloads/

  1. Run php-5.2.9-1-win32-installer.msi and wait.
    Fig 1
  2. Click Next
    Fig 2
  3. Check I accept the terms in the License Agreement check box, and then click Next
    Fig 3
  4. Change the directory to the D:\PHP\ Click Next
    Note: Do not use recommended directory Program Files, you will run into problems.
    Please take note my root drive is the D Drive, usually it is the C drive.
    Fig 4
  5. Select IIS ISAPI module and click Next
    Fig 5
  6. Expand Extensions and Select MySQL and MySQLi, and then Click Next
    Note: You can select other extensions and/or options you may need.
    Fig 6
  7. Click Install
    Fig 7
  8. Wait
    Fig 8
  9. Click Finish
    Fig 9
  10. Configure IIS to work with PHP

  11. Open IIS Management Console
    Start | Control Panel | Performance and Maintenance | Administrative Tools | Internet Information Services
  12. Select Web Sites in the Left Panel
  13. Right Click your web site in the Right Panel and select Properties.
    Fig 10
  14. Select ISAPI Filters Tab Page
  15. Click Add
  16. Type PHP in the Filter Name Text box.
  17. Browse to the installation directory and select php5isapi.dll
    Fig 11
  18. Click OK
  19. Select Home Directory tab page.
  20. Select the Mappings tab page, and then click the Add button.
  21. Click the Browse button. Browse to the installation directory and change the Files of Type to Dynamic Link libraries (*.dll). Select the file php5isapi.dll and click the Open button
  22. In the Extension text box type .php
  23. In the Limit to: text box type GET, HEAD, POST
  24. Check the Script Engine check box.
  25. Check the Check that file exists check box.
    Fig 12
  26. Click OK
  27. Click OK
  28. Click OK
  29. Important REBOOT your PC before carrying on!
  30. Test PHP

    Test Installation
    Create a new text file called phpinfo.php and paste the following into it. Save it in your wwwroot directory (default is C:\Inetpub\wwwroot )

    <html>
    <head>
    <title>PHP Info<title>
    </head>
    <body>
     <p>This is an HTML line</p>
      <?php
        echo "This is a PHP Line";
      ?>
      <p>
      <?php
        phpinfo();
      ?>
      </p>
    </body>
    </html>

    Open http://localhost/phpinfo.php in a web browser. If you see something like the following, you have successfully installed PHP!
    Fig 13

    When you scroll down you should see the mysql and mysqli extensions.
    Fig 14

    Note: According to some sources mysqli is an improved version of mysql and support some stuff that mysql doesn’t. And mysqli has better security and you can do object oriented programming (OOP).

Happy Coding!

In the next post in this series, we will have a more in-depth look on how to connect a MySQL database in Visual Studio with VB.NET

Previous Posts:
.NET & MySQL Part 1 A list of software required as well as optional software that can be used.
.NET & MySQL Part 2 Install MySQL Server

Version 1.1

24 March 2009 Posted by | .NET & MySQL | , , , , , , | 2 Comments