Bug #16703 Cannot execute stored procedure without access to mysql.proc table

• Submitted: 21 Jan 2006 18:20
• Modified: 21 Feb 2006 8:07
• Reporter: Raymond Roelands
• Status: Duplicate
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 1.0.7
• OS: Windows (Windows XP)
• CPU Architecture: Any

[21 Jan 2006 18:20] Raymond Roelands

Description:
The prepare method in the class storedprocedure executes a select command at the mysql.proc table. By default the user has no rights to this table and every call with parameters will fail.

A solution will be that the sql statement is made without looking up the parameters in the database but create the sql statements based on the parameterlist of the IDBCommand object.

How to repeat:
-Revoke the rights of the db user of the Mysql Schema
-Create a stored procedure with parameters.
-Use the .Net connector to execute this stored procedure using ExecuteNonQuery.

Suggested fix:
Create the sql statement to execute using a stringbuilder while iterating the parameterlist of the IDBCommand.

something like this :
/*
this is a very simple solution, doesn't handle output variables so this is not complete, just an idea.
*/

		public string Prepare (MySqlCommand cmd)
		{
			bool first = true;
			System.IO.StringWriter sw;

			sw = new System.IO.StringWriter ();

			sw.Write ("CALL {0} (", cmd.CommandText);
			
			foreach (MySqlParameter p in cmd.Parameters)
			{
				if (!first)
				{
					sw.Write (", ");
				}
				else
				{
					first = false;
				}
				sw.Write ("'");
				sw.Write (p.Value);
				sw.Write ("'");
			}
			sw.Write (");");

			return sw.ToString ();
			

		}

[21 Feb 2006 8:07] Valeriy Kravchuk

Thank you for a problem report. It is a known problem. Read bug #10640, for example. It just have to be documented properly for now. The real fix will be possible later, after adding a separate source of parameters metadata.