Description:
A parameterized update query that worked fine with ByteFX 0.76 is no longer working with Connector.NET 1.0.2.18525.
The reported error is that a null value is not allowed in one of the fields. When I debug the project, the field in question is not null, it holds a proper value (string).
My guess is that previous parameters in the parameterized query is being escaped incorrectly, causing the compiled query string to be invalid.
I've included the class that throws the exception and the exception text. Notice that in one of the parameterized queries I've used a RegEx to excape the user input. This was necessary in ByteFx version of the driver but not anymore. I've tried both using and not using it with the same effect.
MySql.Data.MySqlClient.MySqlException: #23000Column 'web_active' cannot be null at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at BHMailAgent.Data.UserIMailSaveCmd.Save() in c:\inetpub\wwwroot\bhmailagent\data\userimailsavecmd.cs:line 56 at BHMailAgent.index.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\bhmailagent\index.aspx.cs:line 265
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at BHMailAgent.Data.UserIMailSaveCmd.Save() in c:\inetpub\wwwroot\bhmailagent\data\userimailsavecmd.cs:line 56 at BHMailAgent.index.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\bhmailagent\index.aspx.cs:line 265
using System;
using System.Data;
using System.Text.RegularExpressions;
namespace BHMailAgent.Data
{
/// <summary>
/// Summary description for UserIMailDataCmd.
///
/// Use Like:
///
/// UserIMailSaveCmd saveCmd = new UserIMailSaveCmd( con, user );
/// saveCmd.Save();
///
/// </summary>
public class UserIMailSaveCmd
{
public UserIMailSaveCmd( IDbConnection con, UserIMail user )
{
this.con = con;
this.user = user;
if ( user.DbId == -1 ) // -1 is new user.
{
isNew = true;
}
else
{
isNew = false;
}
save = con.CreateCommand();
save.CommandType = CommandType.Text;
if ( isNew )
{
save.CommandText = saveNewSql;
PrepareAsNew();
}
else
{
save.CommandText = saveExisting;
PrepareAsExisting();
}
}
public bool Save()
{
bool ret = false;
bool wasNew = user.DbId == -1; // -1 means the user is new.
PopUser();
if ( !wasNew || !UserExists() ) // If we're updating a user or the user does not already exist we can procede.
{
save.ExecuteNonQuery();
if ( wasNew )
{
user.DbId = GetDbId( user );
}
ret = true;
}
return ret;
}
private bool UserExists()
{
bool ret = false;
IDbCommand ueCmd = con.CreateCommand();
ueCmd.CommandText = userExistsSql;
ueCmd.CommandType = CommandType.Text;
ueCmd.Parameters.Add( snUserId );
ueCmd.Parameters.Add( snAgentId );
ueCmd.Parameters.Add( snMailAddr );
ueCmd.Prepare();
IDataReader reader = ueCmd.ExecuteReader();
if ( reader.Read() )
ret = reader.GetInt32(0) > 0;
reader.Close();
return ret;
}
public int GetDbId( UserIMail user )
{
int ret = 0;
try
{
IDbCommand dbIdCmd = con.CreateCommand();
dbIdCmd.CommandText = getDbIdSql;
dbIdCmd.CommandType = CommandType.Text;
IDbDataParameter snUserId = dbIdCmd.CreateParameter();
snUserId.DbType = DbType.AnsiString;
snUserId.ParameterName = "@UserID";
snUserId.Value = user.UserId;
dbIdCmd.Parameters.Add( snUserId );
dbIdCmd.Prepare();
IDataReader reader = dbIdCmd.ExecuteReader();
/**
if ( reader.Read() )
ret = reader.GetInt32(0);
**/
while( reader.Read() ) ret++;
reader.Close();
}
catch ( Exception e )
{
/// TODO Log this error.
}
return ret;
}
public string EscapeMySqlUserInput( Match sqlEscape )
{
string ret;
if ( @"\\".Equals( sqlEscape.ToString() ) )
{
ret = @"\\" + sqlEscape.ToString();
}
else
{
ret = @"\" + sqlEscape.ToString();
}
return ret;
}
private void PopUser()
{
snUserId.Value = user.UserId;
snPassword.Value = user.Password;
snFullName.Value = user.FullName;
snFirstName.Value = user.FirstName;
snLastName.Value = user.LastName;
snUserDir.Value = Regex.Replace( user.UserDir, MY_SQL_ESCAPES, new MatchEvaluator( this.EscapeMySqlUserInput ) ); // user.UserDir.Replace( @"\", @"\\" );
snMailAddr.Value = user.MailAddress;
snAgentId.Value = user.AgentId;
snOfficeId.Value = user.OfficeId;
snFirmId.Value = user.FirmId;
snDesignation.Value = user.Designation;
snSpeciality.Value = user.Speciality;
snLink.Value = user.Link;
snPermissions.Value = user.Permissions;
snOldLink.Value = user.OldLink;
if ( user.WebActive )
{
snWebActive.Value = "ACTIVE";
}
else
{
snWebActive.Value = "INACTIVE";
}
if ( !isNew )
{
snId.Value = user.DbId;
}
snDesignation.Value = user.Designation;
save.Prepare(); // Pre-prepare the query for user.
}
private void PrepareAsExisting()
{
PrepareAsNew();
snId = save.CreateParameter();
snId.ParameterName = "@Id";
snId.DbType = DbType.Int32;
save.Parameters.Add( snId );
}
private void PrepareAsNew()
{
snUserId = save.CreateParameter();
snUserId.ParameterName = "@UserID";
snUserId.DbType = DbType.AnsiString;
save.Parameters.Add( snUserId );
snPassword = save.CreateParameter();
snPassword.ParameterName = "@Password";
snPassword.DbType = DbType.AnsiString;
save.Parameters.Add( snPassword );
snFullName = save.CreateParameter();
snFullName.ParameterName = "@FullName";
snFullName.DbType = DbType.AnsiString;
save.Parameters.Add( snFullName );
snFirstName = save.CreateParameter();
snFirstName.ParameterName = "@FirstName";
snFirstName.DbType = DbType.AnsiString;
save.Parameters.Add( snFirstName );
snLastName = save.CreateParameter();
snLastName.ParameterName = "@LastName";
snLastName.DbType = DbType.AnsiString;
save.Parameters.Add( snLastName );
snUserDir = save.CreateParameter();
snUserDir.ParameterName = "@UserDir";
snUserDir.DbType = DbType.AnsiString;
save.Parameters.Add( snUserDir );
snMailAddr = save.CreateParameter();
snMailAddr.ParameterName = "@MailAddr";
snMailAddr.DbType = DbType.AnsiString;
save.Parameters.Add( snMailAddr );
snAgentId = save.CreateParameter();
snAgentId.ParameterName = "@AgentId";
snAgentId.DbType = DbType.Int32;
save.Parameters.Add( snAgentId );
snOfficeId = save.CreateParameter();
snOfficeId.ParameterName = "@OfficeId";
snOfficeId.DbType = DbType.Int32;
save.Parameters.Add( snOfficeId );
snFirmId = save.CreateParameter();
snFirmId.ParameterName = "@FirmId";
snFirmId.DbType = DbType.Int32;
save.Parameters.Add( snFirmId );
snDesignation = save.CreateParameter();
snDesignation.ParameterName = "@Designation";
snFirmId.DbType = DbType.AnsiString;
save.Parameters.Add( snDesignation );
snSpeciality = save.CreateParameter();
snSpeciality.ParameterName = "@Speciality";
snSpeciality.DbType = DbType.AnsiString;
save.Parameters.Add( snSpeciality );
snLink = save.CreateParameter();
snLink.ParameterName = "@Link";
snLink.DbType = DbType.AnsiString;
save.Parameters.Add( snLink );
snPermissions = save.CreateParameter();
snPermissions.ParameterName = "@Permissions";
snPermissions.DbType = DbType.AnsiString;
save.Parameters.Add( snPermissions );
snWebActive = save.CreateParameter();
snWebActive.ParameterName = "@WebActive";
snWebActive.DbType = DbType.AnsiString;
save.Parameters.Add( snWebActive );
snOldLink = save.CreateParameter();
snOldLink.ParameterName = "@OldLink";
snOldLink.DbType = DbType.AnsiString;
save.Parameters.Add( snOldLink );
}
private IDbCommand save;
private static string saveNewSql = @"INSERT INTO `beverly_hanks_com` (`USERID`,`PASSWORD`,`FULLNAME`,`FIRSTNAME`,`LASTNAME`,`USERDIR`,`MAILADDR`,`MAXSIZE`,`MAXMSGS`,`FLAGS`,`TYPE`,`NUMTRIES`,`ATTEMPTTIME`,`NUMTIMESSUSP`,`LASTLOGIN`,`SPNDUSRACCT`,`ICALENBL`,`SPELLENBL`,`NUMSGSTNS`,`IWDEPTH`,`IWOPTIONS`,`SPELLCHKG`,`PRVWSIZE`,`DEFVIEW`,`TIMEZONE`,`STRTDAY`,`DEFCALNM`,`ENTRYGRNL`,`STARTTIME`,`ENDTIME`,`MAXEVNTDISP`,`MAXTSKDISP`,`MAXNOTEDISP`,`MAXDLYRCURCNT`,`MAXWKLYRCURCNT`,`MAXMONRCURCNT`,`MAXYRLYRCURCNT`,`DISPCMPLTDTSKS`,`HOURSCLOCK`,`MAXSRCHRESPPG`,`MAXEVENTTITLELEN`,`MAXTASKTITLELEN`,`CONFIRMONDELETE`,`NUMNOTIFY`,`LASTNOTIFY`,`agentid`,`officeid`,`firmid`,`designation`,`specialty`,`link`,`permissions`,`web_active`,`old_link`) VALUES (@UserId,@PassWord,@FullName,@FirstName,@LastName,@UserDir,@MailAddr,0,0,128,65547,0,0,0,0,0,-1,-1,4,-1,0,0,0,'MONTHLY','S GMT Standard Time',0,'Personal',60,9,18,0,0,0,365,100,36,5,0,12,10,25,10,0,0,0,@AgentId,@OfficeId,@FirmId,@Designation,@Specialty,@Link,@Permissions,@WebActive,@OldLink)";
private static string saveExisting = @"UPDATE beverly_hanks_com SET USERID = @UserId, PASSWORD = @Password, FULLNAME = @FullName, FIRSTNAME = @FirstName, LASTNAME = @LastName, USERDIR = @UserDir, MAILADDR = @MailAddr, agentid = @AgentId, officeid = @OfficeId, firmid = @FirmId, designation = @Designation, specialty = @speciality, link = @Link, permissions = @Permissions, web_active = @WebActive, old_link = @OldLink WHERE id = @Id";
private static string getDbIdSql = @"SELECT id FROM beverly_hanks_com WHERE USERID = @UserId";
private static string userExistsSql = @"SELECT id FROM beverly_hanks_com WHERE USERID = @UserId OR MAILADDR = @MailAddr OR agentid = @AgentId";
IDbDataParameter snUserId = null;
IDbDataParameter snPassword = null;
IDbDataParameter snFullName = null;
IDbDataParameter snFirstName = null;
IDbDataParameter snLastName = null;
IDbDataParameter snUserDir = null;
IDbDataParameter snMailAddr = null;
IDbDataParameter snAgentId = null;
IDbDataParameter snOfficeId = null;
IDbDataParameter snFirmId = null;
IDbDataParameter snDesignation = null;
IDbDataParameter snSpeciality = null;
IDbDataParameter snLink = null;
IDbDataParameter snPermissions = null;
IDbDataParameter snWebActive = null;
IDbDataParameter snOldLink = null;
IDbDataParameter snId = null;
public const string MY_SQL_ESCAPES = "('|\"|\b|\n|\r|\t|^\\\\\\\\|\\\\|%|_)+"; // The ^// rule is to accomodate windows unc path names.
private IDbConnection con = null;
private UserIMail user = null;
private bool isNew = false;
}
}
How to repeat:
Simply use this class to reproduce the error.
Suggested fix:
Give the developer some way to see the end result of a parameterized query once it's been "Prepare()"ed. That way if it is an escape problem the developer can work around it.
Description: A parameterized update query that worked fine with ByteFX 0.76 is no longer working with Connector.NET 1.0.2.18525. The reported error is that a null value is not allowed in one of the fields. When I debug the project, the field in question is not null, it holds a proper value (string). My guess is that previous parameters in the parameterized query is being escaped incorrectly, causing the compiled query string to be invalid. I've included the class that throws the exception and the exception text. Notice that in one of the parameterized queries I've used a RegEx to excape the user input. This was necessary in ByteFx version of the driver but not anymore. I've tried both using and not using it with the same effect. MySql.Data.MySqlClient.MySqlException: #23000Column 'web_active' cannot be null at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at BHMailAgent.Data.UserIMailSaveCmd.Save() in c:\inetpub\wwwroot\bhmailagent\data\userimailsavecmd.cs:line 56 at BHMailAgent.index.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\bhmailagent\index.aspx.cs:line 265 at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at BHMailAgent.Data.UserIMailSaveCmd.Save() in c:\inetpub\wwwroot\bhmailagent\data\userimailsavecmd.cs:line 56 at BHMailAgent.index.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\bhmailagent\index.aspx.cs:line 265 using System; using System.Data; using System.Text.RegularExpressions; namespace BHMailAgent.Data { /// <summary> /// Summary description for UserIMailDataCmd. /// /// Use Like: /// /// UserIMailSaveCmd saveCmd = new UserIMailSaveCmd( con, user ); /// saveCmd.Save(); /// /// </summary> public class UserIMailSaveCmd { public UserIMailSaveCmd( IDbConnection con, UserIMail user ) { this.con = con; this.user = user; if ( user.DbId == -1 ) // -1 is new user. { isNew = true; } else { isNew = false; } save = con.CreateCommand(); save.CommandType = CommandType.Text; if ( isNew ) { save.CommandText = saveNewSql; PrepareAsNew(); } else { save.CommandText = saveExisting; PrepareAsExisting(); } } public bool Save() { bool ret = false; bool wasNew = user.DbId == -1; // -1 means the user is new. PopUser(); if ( !wasNew || !UserExists() ) // If we're updating a user or the user does not already exist we can procede. { save.ExecuteNonQuery(); if ( wasNew ) { user.DbId = GetDbId( user ); } ret = true; } return ret; } private bool UserExists() { bool ret = false; IDbCommand ueCmd = con.CreateCommand(); ueCmd.CommandText = userExistsSql; ueCmd.CommandType = CommandType.Text; ueCmd.Parameters.Add( snUserId ); ueCmd.Parameters.Add( snAgentId ); ueCmd.Parameters.Add( snMailAddr ); ueCmd.Prepare(); IDataReader reader = ueCmd.ExecuteReader(); if ( reader.Read() ) ret = reader.GetInt32(0) > 0; reader.Close(); return ret; } public int GetDbId( UserIMail user ) { int ret = 0; try { IDbCommand dbIdCmd = con.CreateCommand(); dbIdCmd.CommandText = getDbIdSql; dbIdCmd.CommandType = CommandType.Text; IDbDataParameter snUserId = dbIdCmd.CreateParameter(); snUserId.DbType = DbType.AnsiString; snUserId.ParameterName = "@UserID"; snUserId.Value = user.UserId; dbIdCmd.Parameters.Add( snUserId ); dbIdCmd.Prepare(); IDataReader reader = dbIdCmd.ExecuteReader(); /** if ( reader.Read() ) ret = reader.GetInt32(0); **/ while( reader.Read() ) ret++; reader.Close(); } catch ( Exception e ) { /// TODO Log this error. } return ret; } public string EscapeMySqlUserInput( Match sqlEscape ) { string ret; if ( @"\\".Equals( sqlEscape.ToString() ) ) { ret = @"\\" + sqlEscape.ToString(); } else { ret = @"\" + sqlEscape.ToString(); } return ret; } private void PopUser() { snUserId.Value = user.UserId; snPassword.Value = user.Password; snFullName.Value = user.FullName; snFirstName.Value = user.FirstName; snLastName.Value = user.LastName; snUserDir.Value = Regex.Replace( user.UserDir, MY_SQL_ESCAPES, new MatchEvaluator( this.EscapeMySqlUserInput ) ); // user.UserDir.Replace( @"\", @"\\" ); snMailAddr.Value = user.MailAddress; snAgentId.Value = user.AgentId; snOfficeId.Value = user.OfficeId; snFirmId.Value = user.FirmId; snDesignation.Value = user.Designation; snSpeciality.Value = user.Speciality; snLink.Value = user.Link; snPermissions.Value = user.Permissions; snOldLink.Value = user.OldLink; if ( user.WebActive ) { snWebActive.Value = "ACTIVE"; } else { snWebActive.Value = "INACTIVE"; } if ( !isNew ) { snId.Value = user.DbId; } snDesignation.Value = user.Designation; save.Prepare(); // Pre-prepare the query for user. } private void PrepareAsExisting() { PrepareAsNew(); snId = save.CreateParameter(); snId.ParameterName = "@Id"; snId.DbType = DbType.Int32; save.Parameters.Add( snId ); } private void PrepareAsNew() { snUserId = save.CreateParameter(); snUserId.ParameterName = "@UserID"; snUserId.DbType = DbType.AnsiString; save.Parameters.Add( snUserId ); snPassword = save.CreateParameter(); snPassword.ParameterName = "@Password"; snPassword.DbType = DbType.AnsiString; save.Parameters.Add( snPassword ); snFullName = save.CreateParameter(); snFullName.ParameterName = "@FullName"; snFullName.DbType = DbType.AnsiString; save.Parameters.Add( snFullName ); snFirstName = save.CreateParameter(); snFirstName.ParameterName = "@FirstName"; snFirstName.DbType = DbType.AnsiString; save.Parameters.Add( snFirstName ); snLastName = save.CreateParameter(); snLastName.ParameterName = "@LastName"; snLastName.DbType = DbType.AnsiString; save.Parameters.Add( snLastName ); snUserDir = save.CreateParameter(); snUserDir.ParameterName = "@UserDir"; snUserDir.DbType = DbType.AnsiString; save.Parameters.Add( snUserDir ); snMailAddr = save.CreateParameter(); snMailAddr.ParameterName = "@MailAddr"; snMailAddr.DbType = DbType.AnsiString; save.Parameters.Add( snMailAddr ); snAgentId = save.CreateParameter(); snAgentId.ParameterName = "@AgentId"; snAgentId.DbType = DbType.Int32; save.Parameters.Add( snAgentId ); snOfficeId = save.CreateParameter(); snOfficeId.ParameterName = "@OfficeId"; snOfficeId.DbType = DbType.Int32; save.Parameters.Add( snOfficeId ); snFirmId = save.CreateParameter(); snFirmId.ParameterName = "@FirmId"; snFirmId.DbType = DbType.Int32; save.Parameters.Add( snFirmId ); snDesignation = save.CreateParameter(); snDesignation.ParameterName = "@Designation"; snFirmId.DbType = DbType.AnsiString; save.Parameters.Add( snDesignation ); snSpeciality = save.CreateParameter(); snSpeciality.ParameterName = "@Speciality"; snSpeciality.DbType = DbType.AnsiString; save.Parameters.Add( snSpeciality ); snLink = save.CreateParameter(); snLink.ParameterName = "@Link"; snLink.DbType = DbType.AnsiString; save.Parameters.Add( snLink ); snPermissions = save.CreateParameter(); snPermissions.ParameterName = "@Permissions"; snPermissions.DbType = DbType.AnsiString; save.Parameters.Add( snPermissions ); snWebActive = save.CreateParameter(); snWebActive.ParameterName = "@WebActive"; snWebActive.DbType = DbType.AnsiString; save.Parameters.Add( snWebActive ); snOldLink = save.CreateParameter(); snOldLink.ParameterName = "@OldLink"; snOldLink.DbType = DbType.AnsiString; save.Parameters.Add( snOldLink ); } private IDbCommand save; private static string saveNewSql = @"INSERT INTO `beverly_hanks_com` (`USERID`,`PASSWORD`,`FULLNAME`,`FIRSTNAME`,`LASTNAME`,`USERDIR`,`MAILADDR`,`MAXSIZE`,`MAXMSGS`,`FLAGS`,`TYPE`,`NUMTRIES`,`ATTEMPTTIME`,`NUMTIMESSUSP`,`LASTLOGIN`,`SPNDUSRACCT`,`ICALENBL`,`SPELLENBL`,`NUMSGSTNS`,`IWDEPTH`,`IWOPTIONS`,`SPELLCHKG`,`PRVWSIZE`,`DEFVIEW`,`TIMEZONE`,`STRTDAY`,`DEFCALNM`,`ENTRYGRNL`,`STARTTIME`,`ENDTIME`,`MAXEVNTDISP`,`MAXTSKDISP`,`MAXNOTEDISP`,`MAXDLYRCURCNT`,`MAXWKLYRCURCNT`,`MAXMONRCURCNT`,`MAXYRLYRCURCNT`,`DISPCMPLTDTSKS`,`HOURSCLOCK`,`MAXSRCHRESPPG`,`MAXEVENTTITLELEN`,`MAXTASKTITLELEN`,`CONFIRMONDELETE`,`NUMNOTIFY`,`LASTNOTIFY`,`agentid`,`officeid`,`firmid`,`designation`,`specialty`,`link`,`permissions`,`web_active`,`old_link`) VALUES (@UserId,@PassWord,@FullName,@FirstName,@LastName,@UserDir,@MailAddr,0,0,128,65547,0,0,0,0,0,-1,-1,4,-1,0,0,0,'MONTHLY','S GMT Standard Time',0,'Personal',60,9,18,0,0,0,365,100,36,5,0,12,10,25,10,0,0,0,@AgentId,@OfficeId,@FirmId,@Designation,@Specialty,@Link,@Permissions,@WebActive,@OldLink)"; private static string saveExisting = @"UPDATE beverly_hanks_com SET USERID = @UserId, PASSWORD = @Password, FULLNAME = @FullName, FIRSTNAME = @FirstName, LASTNAME = @LastName, USERDIR = @UserDir, MAILADDR = @MailAddr, agentid = @AgentId, officeid = @OfficeId, firmid = @FirmId, designation = @Designation, specialty = @speciality, link = @Link, permissions = @Permissions, web_active = @WebActive, old_link = @OldLink WHERE id = @Id"; private static string getDbIdSql = @"SELECT id FROM beverly_hanks_com WHERE USERID = @UserId"; private static string userExistsSql = @"SELECT id FROM beverly_hanks_com WHERE USERID = @UserId OR MAILADDR = @MailAddr OR agentid = @AgentId"; IDbDataParameter snUserId = null; IDbDataParameter snPassword = null; IDbDataParameter snFullName = null; IDbDataParameter snFirstName = null; IDbDataParameter snLastName = null; IDbDataParameter snUserDir = null; IDbDataParameter snMailAddr = null; IDbDataParameter snAgentId = null; IDbDataParameter snOfficeId = null; IDbDataParameter snFirmId = null; IDbDataParameter snDesignation = null; IDbDataParameter snSpeciality = null; IDbDataParameter snLink = null; IDbDataParameter snPermissions = null; IDbDataParameter snWebActive = null; IDbDataParameter snOldLink = null; IDbDataParameter snId = null; public const string MY_SQL_ESCAPES = "('|\"|\b|\n|\r|\t|^\\\\\\\\|\\\\|%|_)+"; // The ^// rule is to accomodate windows unc path names. private IDbConnection con = null; private UserIMail user = null; private bool isNew = false; } } How to repeat: Simply use this class to reproduce the error. Suggested fix: Give the developer some way to see the end result of a parameterized query once it's been "Prepare()"ed. That way if it is an escape problem the developer can work around it.