' in SQL string 2

[WynneSMI]

I have a ASP.NET web app written in C#. I have a problem when a user will enter an apostrophe (') it has problems writing to the db because SQL uses ' in it's statements. How do I get around this problem? Is there a way to search a string and replace the ' with the ascii code (character 39)???? Any help is appreciated. Thanks.

 

[RiverGuy]

There are a few options.

One is to use stored procedures, and pass the string as a parameter. This makes the code cleaner and more secure too.

Two is if you want to replace the character, I think you can use double single quotes. Like so:
string s;
s = "Hello World, this is a single quote '";
s = s.Replace("'", "''"
MessageBox.Show(s);

 

[WynneSMI]

Thanks RiverGuy, but is there a way to replace the ' with the actual ascii character so I can write the info to the database and retrieve it with the '?!? Basically I'm looking for a way to get around saving strings to a db with an ' in them.

 

[RiverGuy]

I don't understand what you mean. You do or not want to save the single quotes?

You can use a unicode character I believe which would be '\u0027'.

But I think the database will still have trouble is you do a s = s.Replace("'", '\u0027');

For more info, in the help section of your VS.Net IDE, look up the article title "Putting Quotation Marks in a String Programmatically (Windows Forms)".

 

[WynneSMI]

Thanks for your help. I think I'm on the right track now.

 

[chiph]

WynneSMI -

Using string replacement on these characters will work, but you'll actually see performance improvements by using ADO.NET Parameter objects. When you use them, not only do they take care of those annoying single-quotes and double-quote characters, it allows the database to store your query in it's procedure cache, so the next time you run it, it doesn't have to parse it first (which can be very expensive in terms of CPU time).

To use them, do something like this:

public CUser GetAUser(string ConnectStringHere, int UserIDToGetHere) 
{
SqlConnection sqlConn = null;
SqlCommand sqlComm = null;
SqlDataReader sqlDR = null;
CUser MyUser = null;
StringBuilder MySQL = new StringBuilder();
try 
{
	sqlConn = new SqlConnection(ConnectStringHere);

	MySQL.Append(" SELECT FirstName, LastName,");
	MySQL.Append("   UserName, Role,");
	MySQL.Append("   PhoneNumber, LastLoginDate,");
	MySQL.Append("   BadLoginCount");
	MySQL.Append(" FROM dbo.tbl_User");
	MySQL.Append(" WHERE IsActive = @ActiveFlag");
	MySQL.Append("   AND UserID = @UserID");

	sqlComm = new SqlCommand();
	sqlComm.CommandType = CommandType.Text;
	sqlComm.CommandText = MySQL.ToString();
	sqlComm.Parameters.Add("@ActiveFlag", SqlDbType.Bit).Value = true;
	sqlComm.Parameters.Add("@UserID", SqlDbType.Int).Value = UserIDToGetHere;
	sqlComm.Connection = sqlConn;

	sqlConn.Open();
	sqlDR = sqlComm.ExecuteReader();

	if (sqlDR.Read()) 
	{
		MyUser = new CUser();

		MyUser.UserId = UserIDToGetHere;
		MyUser.FirstName = sqlDR.GetString(0).Trim();
		MyUser.LastName = sqlDR.GetString(1).Trim();
		MyUser.UserName = sqlDR.GetString(2).Trim();
		MyUser.Role = sqlDR.GetString(3).Trim();
		MyUser.PhoneNumber = sqlDR.GetString(4).Trim();
		MyUser.LastLoginDate = sqlDR.GetDateTime(5);
		MyUser.BadLoginCount = sqlDR.GetInt(6);
	}
}
catch (Exception ex) 
{
	Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
}
finally 
{
	if (sqlDR != null) 
	{
		if (!sqlDR.IsClosed) 
		{
			sqlDR.Close();
		}
	}
	if (sqlConn != null) 
	{
		if (sqlConn.State == ConnectionState.Open)
		{
			sqlConn.Close();
		}
		sqlConn.Dispose();
	}
	return MyUser;
}
}

Note that using the "dbo." prefix results in faster access as the database (I'm assuming SQL Server or MSDE) doesn't have to verify the owner of the table.

Hope this gets you started.

Chip H.