mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs

   1 //
   2 // Mono.Data.Tds.Protocol.Tds70.cs
   3 //
   4 // Author:
   5 //   Tim Coleman (tim@timcoleman.com)
   6 //   Diego Caravana (diego@toth.it)
   7 //   Sebastien Pouliot (sebastien@ximian.com)
   8 //   Daniel Morgan (danielmorgan@verizon.net)
   9 //
  10 // Copyright (C) 2002 Tim Coleman
  11 // Portions (C) 2003 Motus Technologies Inc. (http://www.motus.com)
  12 // Portions (C) 2003 Daniel Morgan
  13 //
  14
  15 //
  16 // Permission is hereby granted, free of charge, to any person obtaining
  17 // a copy of this software and associated documentation files (the
  18 // "Software"), to deal in the Software without restriction, including
  19 // without limitation the rights to use, copy, modify, merge, publish,
  20 // distribute, sublicense, and/or sell copies of the Software, and to
  21 // permit persons to whom the Software is furnished to do so, subject to
  22 // the following conditions:
  23 //
  24 // The above copyright notice and this permission notice shall be
  25 // included in all copies or substantial portions of the Software.
  26 //
  27 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  28 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  29 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  30 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  31 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  32 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  33 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  34 //
  35
  36 using Mono.Security.Protocol.Ntlm;
  37 using System;
  38 using System.Text;
  39
  40 namespace Mono.Data.Tds.Protocol {
  41         public class Tds70 : Tds
  42         {
  43                 #region Fields
  44
  45                 public readonly static TdsVersion Version = TdsVersion.tds70;
  46
  47                 #endregion // Fields
  48
  49                 #region Constructors
  50
  51                 public Tds70 (string server, int port)
  52                         : this (server, port, 512, 15)
  53                 {
  54                 }
  55
  56                 public Tds70 (string server, int port, int packetSize, int timeout)
  57                         : base (server, port, packetSize, timeout, Version)
  58                 {
  59                 }
  60
  61                 #endregion // Constructors
  62
  63                 #region Methods
  64
  65                 private string BuildExec (string sql)
  66                 {
  67                         string esql = sql.Replace ("'", "''"); // escape single quote
  68                         if (Parameters != null && Parameters.Count > 0)
  69                                 return BuildProcedureCall (String.Format ("sp_executesql N'{0}', N'{1}', ", esql, BuildPreparedParameters ()));
  70                         else
  71                                 return BuildProcedureCall (String.Format ("sp_executesql N'{0}'", esql));
  72                 }
  73
  74                 private string BuildParameters ()
  75                 {
  76                         if (Parameters == null || Parameters.Count == 0)
  77                                 return String.Empty;
  78
  79                         StringBuilder result = new StringBuilder ();
  80                         foreach (TdsMetaParameter p in Parameters) {
  81                                 if (p.Direction != TdsParameterDirection.ReturnValue) {
  82                                 if (result.Length > 0)
  83                                         result.Append (", ");
  84                                         if (p.Direction == TdsParameterDirection.InputOutput)
  85                                                 result.Append (String.Format("{0}={0} output", p.ParameterName));
  86                                         else
  87                                 result.Append (FormatParameter (p));
  88                         }
  89                         }
  90                         return result.ToString ();
  91                 }
  92
  93                 private string BuildPreparedParameters ()
  94                 {
  95                         StringBuilder parms = new StringBuilder ();
  96                         foreach (TdsMetaParameter p in Parameters) {
  97                                 if (parms.Length > 0)
  98                                         parms.Append (", ");
  99                                 parms.Append (p.Prepare ());
 100                                 if (p.Direction == TdsParameterDirection.Output)
 101                                         parms.Append (" output");
 102                         }
 103                         return parms.ToString ();
 104                 }
 105
 106                 private string BuildPreparedQuery (string id)
 107                 {
 108                         return BuildProcedureCall (String.Format ("sp_execute {0},", id));
 109                 }
 110
 111                 private string BuildProcedureCall (string procedure)
 112                 {
 113                         string exec = String.Empty;
 114
 115                         StringBuilder declare = new StringBuilder ();
 116                         StringBuilder select = new StringBuilder ();
 117                         StringBuilder set = new StringBuilder ();
 118
 119                         int count = 0;
 120                         if (Parameters != null) {
 121                                 foreach (TdsMetaParameter p in Parameters) {
 122                                         if (p.Direction != TdsParameterDirection.Input) {
 123
 124                                                 if (count == 0)
 125                                                         select.Append ("select ");
 126                                                 else
 127                                                         select.Append (", ");
 128                                                 select.Append (p.ParameterName);
 129
 130                                                 declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
 131
 132                                                 if (p.Direction != TdsParameterDirection.ReturnValue) {
 133                                                         if( p.Direction == TdsParameterDirection.InputOutput )
 134                                                                 set.Append (String.Format ("set {0}\n", FormatParameter(p)));
 135                                                         else
 136                                                 set.Append (String.Format ("set {0}=NULL\n", p.ParameterName));
 137                                                 }
 138
 139                                                 count += 1;
 140                                         }
 141
 142                                         if (p.Direction == TdsParameterDirection.ReturnValue) {
 143                                                 exec = p.ParameterName + "=";
 144                                         }
 145                                 }
 146                         }
 147                         if (count > 0 || exec.Length > 0)
 148                                 exec = "exec " + exec;
 149
 150                         return String.Format ("{0}{1}{2}{3} {4}\n{5}", declare.ToString (), set.ToString (), exec, procedure, BuildParameters (), select.ToString ());
 151                 }
 152
 153                 public override bool Connect (TdsConnectionParameters connectionParameters)
 154                 {
 155                         if (IsConnected)
 156                                 throw new InvalidOperationException ("The connection is already open.");
 157
 158                         connectionParms = connectionParameters;
 159
 160                         SetLanguage (connectionParameters.Language);
 161                         SetCharset ("utf-8");
 162
 163                         byte[] empty = new byte[0];
 164                         short authLen = 0;
 165                         byte pad = (byte) 0;
 166
 167                         byte[] domainMagic =  { 6, 0x7d, 0x0f, 0xfd, 0xff, 0x0, 0x0, 0x0,
 168                                                                         0x0, 0xe0, 0x83, 0x0, 0x0,
 169                                                                         0x68, 0x01, 0x00, 0x00, 0x09, 0x04, 0x00, 0x00 };
 170                         byte[] sqlserverMagic = { 6, 0x83, 0xf2, 0xf8,
 171                                                                                 0xff, 0x0, 0x0, 0x0,
 172                                                                                 0x0, 0xe0, 0x03, 0x0,
 173                                                                                 0x0, 0x88, 0xff, 0xff, 0xff, 0x36,
 174                                                                                 0x04, 0x00, 0x00 };
 175                         byte[] magic = null;
 176
 177                         if (connectionParameters.DomainLogin == true)
 178                                 magic = domainMagic;
 179                         else
 180                                 magic = sqlserverMagic;
 181
 182                         string username = connectionParameters.User;
 183
 184                         string domain = Environment.UserDomainName;
 185                         domain = connectionParameters.DefaultDomain = Environment.UserDomainName;
 186
 187                         int idx = 0;
 188                         if ((idx = username.IndexOf (@"\")) > -1) {
 189                                 domain = username.Substring (0, idx);
 190                                 username = username.Substring (idx + 1);
 191
 192                                 connectionParameters.DefaultDomain = domain;
 193                                 connectionParameters.User = username;
 194                         }
 195
 196                         short partialPacketSize = (short) (86 + (
 197                                 connectionParameters.Hostname.Length +
 198                                 connectionParameters.ApplicationName.Length +
 199                                 DataSource.Length +
 200                                 connectionParameters.LibraryName.Length +
 201                                 Language.Length +
 202                                 connectionParameters.Database.Length) * 2);
 203
 204                         if(connectionParameters.DomainLogin == true) {
 205                                 authLen = ((short) (32 + (connectionParameters.Hostname.Length +
 206                                         domain.Length)));
 207                                 partialPacketSize += authLen;
 208                         }
 209                         else
 210                                 partialPacketSize += ((short) ((username.Length + connectionParameters.Password.Length) * 2));
 211
 212                         short totalPacketSize = (short) partialPacketSize;
 213
 214                         Comm.StartPacket (TdsPacketType.Logon70);
 215
 216                         Comm.Append (totalPacketSize);
 217                         Comm.Append (empty, 5, pad);
 218
 219                         Comm.Append ((byte) 0x70); // TDS Version 7
 220                         Comm.Append (empty, 3, pad);
 221                         Comm.Append (empty, 4, pad);
 222                         Comm.Append (magic);
 223
 224                         short curPos = 86;
 225
 226                         // Hostname
 227                         Comm.Append (curPos);
 228                         Comm.Append ((short) connectionParameters.Hostname.Length);
 229                         curPos += (short) (connectionParameters.Hostname.Length * 2);
 230
 231                         if(connectionParameters.DomainLogin.Equals(true))
 232                         {
 233                                 Comm.Append((short)0);
 234                                 Comm.Append((short)0);
 235                                 Comm.Append((short)0);
 236                                 Comm.Append((short)0);
 237                         }
 238                         else
 239                         {
 240                                 // Username
 241                                 Comm.Append (curPos);
 242                                 Comm.Append ((short) username.Length);
 243                                 curPos += ((short) (username.Length * 2));
 244
 245                                 // Password
 246                                 Comm.Append (curPos);
 247                                 Comm.Append ((short) connectionParameters.Password.Length);
 248                                 curPos += (short) (connectionParameters.Password.Length * 2);
 249                         }
 250
 251                         // AppName
 252                         Comm.Append (curPos);
 253                         Comm.Append ((short) connectionParameters.ApplicationName.Length);
 254                         curPos += (short) (connectionParameters.ApplicationName.Length * 2);
 255
 256                         // Server Name
 257                         Comm.Append (curPos);
 258                         Comm.Append ((short) DataSource.Length);
 259                         curPos += (short) (DataSource.Length * 2);
 260
 261                         // Unknown
 262                         Comm.Append ((short) 0);
 263                         Comm.Append ((short) 0);
 264
 265                         // Library Name
 266                         Comm.Append (curPos);
 267                         Comm.Append ((short) connectionParameters.LibraryName.Length);
 268                         curPos += (short) (connectionParameters.LibraryName.Length * 2);
 269
 270                         // Language
 271                         Comm.Append (curPos);
 272                         Comm.Append ((short) Language.Length);
 273                         curPos += (short) (Language.Length * 2);
 274
 275                         // Database
 276                         Comm.Append (curPos);
 277                         Comm.Append ((short) connectionParameters.Database.Length);
 278                         curPos += (short) (connectionParameters.Database.Length * 2);
 279
 280                         // MAC Address
 281                         Comm.Append((byte) 0);
 282                         Comm.Append((byte) 0);
 283                         Comm.Append((byte) 0);
 284                         Comm.Append((byte) 0);
 285                         Comm.Append((byte) 0);
 286                         Comm.Append((byte) 0);
 287
 288                         // Authentication Stuff
 289                         Comm.Append ((short) curPos);
 290                         if (connectionParameters.DomainLogin == true)
 291                         {
 292                                 Comm.Append ((short) authLen);
 293                                 curPos += (short) authLen;
 294                         }
 295                         else
 296                                 Comm.Append ((short) 0);
 297
 298                         // Unknown
 299                         Comm.Append (curPos);
 300                         Comm.Append ((short) 0);
 301
 302                         // Connection Parameters
 303                         Comm.Append (connectionParameters.Hostname);
 304                         if (connectionParameters.DomainLogin == false)
 305                         {
 306                                 // SQL Server Authentication
 307                                 Comm.Append (connectionParameters.User);
 308                                 string scrambledPwd = EncryptPassword (connectionParameters.Password);
 309                                 Comm.Append (scrambledPwd);
 310                         }
 311                         Comm.Append (connectionParameters.ApplicationName);
 312                         Comm.Append (DataSource);
 313                         Comm.Append (connectionParameters.LibraryName);
 314                         Comm.Append (Language);
 315                         Comm.Append (connectionParameters.Database);
 316
 317                         if (connectionParameters.DomainLogin)
 318                         {
 319                                 // the rest of the packet is NTLMSSP authentication
 320                                 Type1Message msg = new Type1Message ();
 321                                 msg.Domain = domain;
 322                                 msg.Host = connectionParameters.Hostname;
 323                                 msg.Flags = NtlmFlags.NegotiateUnicode |
 324                                         NtlmFlags.NegotiateNtlm |
 325                                         NtlmFlags.NegotiateDomainSupplied |
 326                                         NtlmFlags.NegotiateWorkstationSupplied |
 327                                         NtlmFlags.NegotiateAlwaysSign; // 0xb201
 328                                 Comm.Append (msg.GetBytes ());
 329                         }
 330
 331                         Comm.SendPacket ();
 332                         MoreResults = true;
 333                         SkipToEnd ();
 334
 335                         return IsConnected;
 336                 }
 337
 338                 private static string EncryptPassword (string pass)
 339                 {
 340                         int xormask = 0x5a5a;
 341                         int len = pass.Length;
 342                         char[] chars = new char[len];
 343
 344                         for (int i = 0; i < len; ++i) {
 345                                 int c = ((int) (pass[i])) ^ xormask;
 346                                 int m1 = (c >> 4) & 0x0f0f;
 347                                 int m2 = (c << 4) & 0xf0f0;
 348                                 chars[i] = (char) (m1 | m2);
 349                         }
 350
 351                         return new String (chars);
 352                 }
 353
 354                 public override bool Reset ()
 355                 {
 356                         try
 357                         {
 358                                 ExecProc ("exec sp_reset_connection");
 359                                 return true;
 360                         }
 361                         catch
 362                         {
 363                                 Console.WriteLine ("Error reseting");
 364                                 return false;
 365                         }
 366                 }
 367
 368                 public override void ExecPrepared (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 369                 {
 370                         Parameters = parameters;
 371                         ExecuteQuery (BuildPreparedQuery (commandText), timeout, wantResults);
 372                 }
 373
 374                 public override void ExecProc (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 375                 {
 376                         Parameters = parameters;
 377                         ExecuteQuery (BuildProcedureCall (commandText), timeout, wantResults);
 378                 }
 379
 380                 public override void Execute (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 381                 {
 382                         Parameters = parameters;
 383                         string sql = commandText;
 384                         if (wantResults || (Parameters != null && Parameters.Count > 0))
 385                                 sql = BuildExec (commandText);
 386                         ExecuteQuery (sql, timeout, wantResults);
 387                 }
 388
 389                 private bool IsBlobType (TdsColumnType columnType)
 390                 {
 391                         return (columnType == TdsColumnType.Text || columnType == TdsColumnType.Image || columnType == TdsColumnType.NText);
 392                 }
 393
 394                 private bool IsLargeType (TdsColumnType columnType)
 395                 {
 396                         return (columnType == TdsColumnType.NChar || (byte) columnType > 128);
 397                 }
 398
 399                 private string FormatParameter (TdsMetaParameter parameter)
 400                 {
 401                         if (parameter.Direction == TdsParameterDirection.Output)
 402                                 return String.Format ("{0}={0} output", parameter.ParameterName);
 403
 404                         if (parameter.Value == null || parameter.Value == DBNull.Value)
 405                                 return parameter.ParameterName + "=NULL";
 406
 407                         string value = null;
 408                         switch (parameter.TypeName) {
 409                         case "bigint":
 410                         case "decimal":
 411                         case "float":
 412                         case "int":
 413                         case "money":
 414                         case "real":
 415                         case "smallint":
 416                         case "smallmoney":
 417                         case "tinyint":
 418                                 object paramValue = parameter.Value;
 419                                 Type paramType = paramValue.GetType ();
 420                                 if (paramType.IsEnum)
 421                                         paramValue = Convert.ChangeType (paramValue,
 422                                                 Type.GetTypeCode (paramType));
 423                                 value = paramValue.ToString ();
 424                                 break;
 425                         case "nvarchar":
 426                         case "nchar":
 427                                 value = String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 428                                 break;
 429                         case "uniqueidentifier":
 430                                 value = String.Format ("0x{0}", ((Guid) parameter.Value).ToString ("N"));
 431                                 break;
 432                         case "bit":
 433                                 if (parameter.Value.GetType () == typeof (bool))
 434                                         value = (((bool) parameter.Value) ? "0x1" : "0x0");
 435                                 else
 436                                         value = parameter.Value.ToString ();
 437
 438                                 break;
 439                         case "image":
 440                         case "binary":
 441                         case "varbinary":
 442                                 value = String.Format ("0x{0}", BitConverter.ToString ((byte[]) parameter.Value).Replace ("-", "").ToLower ());
 443                                 break;
 444                         default:
 445                                 value = String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 446                                 break;
 447                         }
 448
 449                         return parameter.ParameterName + "=" + value;
 450                 }
 451
 452                 public override string Prepare (string commandText, TdsMetaParameterCollection parameters)
 453                 {
 454                         Parameters = parameters;
 455
 456                         TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
 457                         TdsMetaParameter parm = new TdsMetaParameter ("@P1", "int", null);
 458                         parm.Direction = TdsParameterDirection.Output;
 459                         parms.Add (parm);
 460
 461                         parms.Add (new TdsMetaParameter ("@P2", "nvarchar", BuildPreparedParameters ()));
 462                         parms.Add (new TdsMetaParameter ("@P3", "nvarchar", commandText));
 463
 464                         ExecProc ("sp_prepare", parms, 0, true);
 465                         SkipToEnd ();
 466                         if (ColumnValues [0] == null || ColumnValues [0] == DBNull.Value)
 467                                 throw new TdsInternalException ();
 468                         return ColumnValues [0].ToString ();
 469                 }
 470
 471                 protected override TdsDataColumnCollection ProcessColumnInfo ()
 472                 {
 473                         TdsDataColumnCollection result = new TdsDataColumnCollection ();
 474                         int numColumns = Comm.GetTdsShort ();
 475
 476                         for (int i = 0; i < numColumns; i += 1) {
 477                                 byte[] flagData = new byte[4];
 478                                 for (int j = 0; j < 4; j += 1)
 479                                         flagData[j] = Comm.GetByte ();
 480
 481                                 bool nullable = (flagData[2] & 0x01) > 0;
 482                                 bool caseSensitive = (flagData[2] & 0x02) > 0;
 483                                 bool writable = (flagData[2] & 0x0c) > 0;
 484                                 bool autoIncrement = (flagData[2] & 0x10) > 0;
 485                                 bool isIdentity = (flagData[2] & 0x10) > 0;
 486
 487                                 TdsColumnType columnType = (TdsColumnType) (Comm.GetByte () & 0xff);
 488                                 if ((byte) columnType == 0xef)
 489                                         columnType = TdsColumnType.NChar;
 490
 491                                 byte xColumnType = 0;
 492                                 if (IsLargeType (columnType)) {
 493                                         xColumnType = (byte) columnType;
 494                                         if (columnType != TdsColumnType.NChar)
 495                                                 columnType -= 128;
 496                                 }
 497
 498                                 int columnSize;
 499                                 string tableName = null;
 500
 501                                 if (IsBlobType (columnType)) {
 502                                         columnSize = Comm.GetTdsInt ();
 503                                         tableName = Comm.GetString (Comm.GetTdsShort ());
 504                                 }
 505
 506                                 else if (IsFixedSizeColumn (columnType))
 507                                         columnSize = LookupBufferSize (columnType);
 508                                 else if (IsLargeType ((TdsColumnType) xColumnType))
 509                                         columnSize = Comm.GetTdsShort ();
 510                                 else
 511                                         columnSize = Comm.GetByte () & 0xff;
 512
 513                                 byte precision = 0;
 514                                 byte scale = 0;
 515
 516                                 switch (columnType) {
 517                                 case TdsColumnType.NText:
 518                                 case TdsColumnType.NChar:
 519                                 case TdsColumnType.NVarChar:
 520                                         columnSize /= 2;
 521                                         break;
 522                                 case TdsColumnType.Decimal:
 523                                 case TdsColumnType.Numeric:
 524                                         precision = Comm.GetByte ();
 525                                         scale = Comm.GetByte ();
 526                                         break;
 527                                 }
 528
 529                                 string columnName = Comm.GetString (Comm.GetByte ());
 530
 531                                 int index = result.Add (new TdsDataColumn ());
 532                                 result[index]["AllowDBNull"] = nullable;
 533                                 result[index]["ColumnName"] = columnName;
 534                                 result[index]["ColumnSize"] = columnSize;
 535                                 result[index]["ColumnType"] = columnType;
 536                                 result[index]["IsIdentity"] = isIdentity;
 537                                 result[index]["IsReadOnly"] = !writable;
 538                                 result[index]["NumericPrecision"] = precision;
 539                                 result[index]["NumericScale"] = scale;
 540                                 result[index]["BaseTableName"] = tableName;
 541                         }
 542
 543                         return result;
 544                 }
 545
 546                 public override void Unprepare (string statementId)
 547                 {
 548                         TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
 549                         parms.Add (new TdsMetaParameter ("@P1", "int", Int32.Parse (statementId)));
 550                         ExecProc ("sp_unprepare", parms, 0, false);
 551                 }
 552
 553                 #endregion // Methods
 554         }
 555 }