1# Copyright (c) 2003-2016 CORE Security Technologies 2# 3# This software is provided under under a slightly modified version 4# of the Apache Software License. See the accompanying LICENSE file 5# for more information. 6# 7# Description: [MS-TDS] & [MC-SQLR] implementation. 8# 9# ToDo: 10# [ ] Add all the tokens left 11# [ ] parseRow should be rewritten and add support for all the SQL types in a 12# good way. Right now it just supports a few types. 13# [ ] printRows is crappy, just an easy way to print the rows. It should be 14# rewritten to output like a normal SQL client 15# 16# Author: 17# Alberto Solino (@agsolino) 18# 19 20 21import struct 22import socket 23import select 24import random 25import binascii 26import math 27import datetime 28import sys 29import string 30 31from impacket import ntlm, uuid, LOG 32from impacket.structure import Structure 33 34try: 35 import OpenSSL 36 from OpenSSL import SSL, crypto 37except: 38 LOG.critical("pyOpenSSL is not installed, can't continue") 39 raise 40 41# We need to have a fake Logger to be compatible with the way Impact 42# prints information. Outside Impact it's just a print. Inside 43# we will receive the Impact logger instance to print row information 44# The rest it processed through the standard impacket logging mech. 45class DummyPrint: 46 def logMessage(self,message): 47 print message 48 49# MC-SQLR Constants and Structures 50SQLR_PORT = 1434 51SQLR_CLNT_BCAST_EX = 0x02 52SQLR_CLNT_UCAST_EX = 0x03 53SQLR_CLNT_UCAST_INST= 0x04 54SQLR_CLNT_UCAST_DAC = 0x0f 55 56 57class SQLR(Structure): 58 commonHdr = ( 59 ('OpCode','B'), 60 ) 61 62class SQLR_UCAST_INST(SQLR): 63 structure = ( 64 ('Instance',':') 65 ) 66 def __init__(self, data = None): 67 SQLR.__init__(self,data) 68 if data is not None: 69 self['OpCode'] = SQLR_CLNT_UCAST_INST 70 71class SQLR_UCAST_DAC(SQLR): 72 structure = ( 73 ('Protocol', 'B=1'), 74 ('Instance', ':'), 75 ) 76 def __init__(self, data = None): 77 SQLR.__init__(self,data) 78 if data is not None: 79 self['OpCode'] = SQLR_CLNT_UCAST_DAC 80 81class SQLR_Response(SQLR): 82 structure = ( 83 ('Size','<H'), 84 ('_Data','_-Data','self["Size"]'), 85 ('Data',':'), 86 ) 87 88class SQLErrorException(Exception): 89 pass 90 91# TDS Constants and Structures 92 93# TYPE constants 94TDS_SQL_BATCH = 1 95TDS_PRE_TDS_LOGIN = 2 96TDS_RPC = 3 97TDS_TABULAR = 4 98TDS_ATTENTION = 6 99TDS_BULK_LOAD_DATA = 7 100TDS_TRANSACTION = 14 101TDS_LOGIN7 = 16 102TDS_SSPI = 17 103TDS_PRE_LOGIN = 18 104 105# Status constants 106TDS_STATUS_NORMAL = 0 107TDS_STATUS_EOM = 1 108TDS_STATUS_RESET_CONNECTION = 8 109TDS_STATUS_RESET_SKIPTRANS = 16 110 111# Encryption 112TDS_ENCRYPT_OFF = 0 113TDS_ENCRYPT_ON = 1 114TDS_ENCRYPT_NOT_SUP = 2 115TDS_ENCRYPT_REQ = 3 116 117# Option 2 Flags 118TDS_INTEGRATED_SECURITY_ON = 0x80 119TDS_INIT_LANG_FATAL = 0x01 120TDS_ODBC_ON = 0x02 121 122# Token Types 123TDS_ALTMETADATA_TOKEN = 0x88 124TDS_ALTROW_TOKEN = 0xD3 125TDS_COLMETADATA_TOKEN = 0x81 126TDS_COLINFO_TOKEN = 0xA5 127TDS_DONE_TOKEN = 0xFD 128TDS_DONEPROC_TOKEN = 0xFE 129TDS_DONEINPROC_TOKEN = 0xFF 130TDS_ENVCHANGE_TOKEN = 0xE3 131TDS_ERROR_TOKEN = 0xAA 132TDS_INFO_TOKEN = 0xAB 133TDS_LOGINACK_TOKEN = 0xAD 134TDS_NBCROW_TOKEN = 0xD2 135TDS_OFFSET_TOKEN = 0x78 136TDS_ORDER_TOKEN = 0xA9 137TDS_RETURNSTATUS_TOKEN = 0x79 138TDS_RETURNVALUE_TOKEN = 0xAC 139TDS_ROW_TOKEN = 0xD1 140TDS_SSPI_TOKEN = 0xED 141TDS_TABNAME_TOKEN = 0xA4 142 143# ENVCHANGE Types 144TDS_ENVCHANGE_DATABASE = 1 145TDS_ENVCHANGE_LANGUAGE = 2 146TDS_ENVCHANGE_CHARSET = 3 147TDS_ENVCHANGE_PACKETSIZE = 4 148TDS_ENVCHANGE_UNICODE = 5 149TDS_ENVCHANGE_UNICODE_DS = 6 150TDS_ENVCHANGE_COLLATION = 7 151TDS_ENVCHANGE_TRANS_START = 8 152TDS_ENVCHANGE_TRANS_COMMIT = 9 153TDS_ENVCHANGE_ROLLBACK = 10 154TDS_ENVCHANGE_DTC = 11 155 156# Column types 157# FIXED-LEN Data Types 158TDS_NULL_TYPE = 0x1F 159TDS_INT1TYPE = 0x30 160TDS_BITTYPE = 0x32 161TDS_INT2TYPE = 0x34 162TDS_INT4TYPE = 0x38 163TDS_DATETIM4TYPE = 0x3A 164TDS_FLT4TYPE = 0x3B 165TDS_MONEYTYPE = 0x3C 166TDS_DATETIMETYPE = 0x3D 167TDS_FLT8TYPE = 0x3E 168TDS_MONEY4TYPE = 0x7A 169TDS_INT8TYPE = 0x7F 170 171# VARIABLE-Len Data Types 172TDS_GUIDTYPE = 0x24 173TDS_INTNTYPE = 0x26 174TDS_DECIMALTYPE = 0x37 175TDS_NUMERICTYPE = 0x3F 176TDS_BITNTYPE = 0x68 177TDS_DECIMALNTYPE = 0x6A 178TDS_NUMERICNTYPE = 0x6C 179TDS_FLTNTYPE = 0x6D 180TDS_MONEYNTYPE = 0x6E 181TDS_DATETIMNTYPE = 0x6F 182TDS_DATENTYPE = 0x28 183TDS_TIMENTYPE = 0x29 184TDS_DATETIME2NTYPE = 0x2A 185TDS_DATETIMEOFFSETNTYPE = 0x2B 186TDS_CHARTYPE = 0x2F 187TDS_VARCHARTYPE = 0x27 188TDS_BINARYTYPE = 0x2D 189TDS_VARBINARYTYPE = 0x25 190TDS_BIGVARBINTYPE = 0xA5 191TDS_BIGVARCHRTYPE = 0xA7 192TDS_BIGBINARYTYPE = 0xAD 193TDS_BIGCHARTYPE = 0xAF 194TDS_NVARCHARTYPE = 0xE7 195TDS_NCHARTYPE = 0xEF 196TDS_XMLTYPE = 0xF1 197TDS_UDTTYPE = 0xF0 198TDS_TEXTTYPE = 0x23 199TDS_IMAGETYPE = 0x22 200TDS_NTEXTTYPE = 0x63 201TDS_SSVARIANTTYPE = 0x62 202 203class TDSPacket(Structure): 204 structure = ( 205 ('Type','<B'), 206 ('Status','<B=1'), 207 ('Length','>H=8+len(Data)'), 208 ('SPID','>H=0'), 209 ('PacketID','<B=0'), 210 ('Window','<B=0'), 211 ('Data',':'), 212 ) 213 214class TDS_PRELOGIN(Structure): 215 structure = ( 216 ('VersionToken','>B=0'), 217 ('VersionOffset','>H'), 218 ('VersionLength','>H=len(self["Version"])'), 219 ('EncryptionToken','>B=0x1'), 220 ('EncryptionOffset','>H'), 221 ('EncryptionLength','>H=1'), 222 ('InstanceToken','>B=2'), 223 ('InstanceOffset','>H'), 224 ('InstanceLength','>H=len(self["Instance"])'), 225 ('ThreadIDToken','>B=3'), 226 ('ThreadIDOffset','>H'), 227 ('ThreadIDLength','>H=4'), 228 ('EndToken','>B=0xff'), 229 ('_Version','_-Version','self["VersionLength"]'), 230 ('Version',':'), 231 ('Encryption','B'), 232 ('_Instance','_-Instance','self["InstanceLength"]-1'), 233 ('Instance',':'), 234 ('ThreadID',':'), 235 ) 236 237 def __str__(self): 238 self['VersionOffset']=21 239 self['EncryptionOffset']=self['VersionOffset'] + len(self['Version']) 240 self['InstanceOffset']=self['EncryptionOffset'] + 1 241 self['ThreadIDOffset']=self['InstanceOffset'] + len(self['Instance']) 242 return Structure.__str__(self) 243 244class TDS_LOGIN(Structure): 245 structure = ( 246 ('Length','<L=0'), 247 ('TDSVersion','>L=0x71'), 248 ('PacketSize','<L=32764'), 249 ('ClientProgVer','>L=7'), 250 ('ClientPID','<L=0'), 251 ('ConnectionID','<L=0'), 252 ('OptionFlags1','<B=0xe0'), 253 ('OptionFlags2','<B'), 254 ('TypeFlags','<B=0'), 255 ('OptionFlags3','<B=0'), 256 ('ClientTimeZone','<L=0'), 257 ('ClientLCID','<L=0'), 258 ('HostNameOffset','<H'), 259 ('HostNameLength','<H=len(self["HostName"])/2'), 260 ('UserNameOffset','<H=0'), 261 ('UserNameLength','<H=len(self["UserName"])/2'), 262 ('PasswordOffset','<H=0'), 263 ('PasswordLength','<H=len(self["Password"])/2'), 264 ('AppNameOffset','<H'), 265 ('AppNameLength','<H=len(self["AppName"])/2'), 266 ('ServerNameOffset','<H'), 267 ('ServerNameLength','<H=len(self["ServerName"])/2'), 268 ('UnusedOffset','<H=0'), 269 ('UnusedLength','<H=0'), 270 ('CltIntNameOffset','<H'), 271 ('CltIntNameLength','<H=len(self["CltIntName"])/2'), 272 ('LanguageOffset','<H=0'), 273 ('LanguageLength','<H=0'), 274 ('DatabaseOffset','<H=0'), 275 ('DatabaseLength','<H=len(self["Database"])/2'), 276 ('ClientID','6s="\x01\x02\x03\x04\x05\x06"'), 277 ('SSPIOffset','<H'), 278 ('SSPILength','<H=len(self["SSPI"])'), 279 ('AtchDBFileOffset','<H'), 280 ('AtchDBFileLength','<H=len(self["AtchDBFile"])/2'), 281 ('HostName',':'), 282 ('UserName',':'), 283 ('Password',':'), 284 ('AppName',':'), 285 ('ServerName',':'), 286 ('CltIntName',':'), 287 ('Database',':'), 288 ('SSPI',':'), 289 ('AtchDBFile',':'), 290 ) 291 def __init__(self,data=None): 292 Structure.__init__(self,data) 293 if data is None: 294 self['UserName'] = '' 295 self['Password'] = '' 296 self['Database'] = '' 297 self['AtchDBFile'] = '' 298 299 def fromString(self, data): 300 Structure.fromString(self, data) 301 if self['HostNameLength'] > 0: 302 self['HostName'] = data[self['HostNameOffset']:][:self['HostNameLength']*2] 303 304 if self['UserNameLength'] > 0: 305 self['UserName'] = data[self['UserNameOffset']:][:self['UserNameLength']*2] 306 307 if self['PasswordLength'] > 0: 308 self['Password'] = data[self['PasswordOffset']:][:self['PasswordLength']*2] 309 310 if self['AppNameLength'] > 0: 311 self['AppName'] = data[self['AppNameOffset']:][:self['AppNameLength']*2] 312 313 if self['ServerNameLength'] > 0: 314 self['ServerName'] = data[self['ServerNameOffset']:][:self['ServerNameLength']*2] 315 316 if self['CltIntNameLength'] > 0: 317 self['CltIntName'] = data[self['CltIntNameOffset']:][:self['CltIntNameLength']*2] 318 319 if self['DatabaseLength'] > 0: 320 self['Database'] = data[self['DatabaseOffset']:][:self['DatabaseLength']*2] 321 322 if self['SSPILength'] > 0: 323 self['SSPI'] = data[self['SSPIOffset']:][:self['SSPILength']*2] 324 325 if self['AtchDBFileLength'] > 0: 326 self['AtchDBFile'] = data[self['AtchDBFileOffset']:][:self['AtchDBFileLength']*2] 327 328 def __str__(self): 329 index = 36+50 330 self['HostNameOffset']= index 331 332 index += len(self['HostName']) 333 334 if self['UserName'] != '': 335 self['UserNameOffset'] = index 336 else: 337 self['UserNameOffset'] = 0 338 339 index += len(self['UserName']) 340 341 if self['Password'] != '': 342 self['PasswordOffset'] = index 343 else: 344 self['PasswordOffset'] = 0 345 346 index += len(self['Password']) 347 348 self['AppNameOffset']= index 349 self['ServerNameOffset']=self['AppNameOffset'] + len(self['AppName']) 350 self['CltIntNameOffset']=self['ServerNameOffset'] + len(self['ServerName']) 351 self['LanguageOffset']=self['CltIntNameOffset'] + len(self['CltIntName']) 352 self['DatabaseOffset']=self['LanguageOffset'] 353 self['SSPIOffset']=self['DatabaseOffset'] + len(self['Database']) 354 self['AtchDBFileOffset']=self['SSPIOffset'] + len(self['SSPI']) 355 return Structure.__str__(self) 356 357class TDS_LOGIN_ACK(Structure): 358 structure = ( 359 ('TokenType','<B'), 360 ('Length','<H'), 361 ('Interface','<B'), 362 ('TDSVersion','<L'), 363 ('ProgNameLen','<B'), 364 ('_ProgNameLen','_-ProgName','self["ProgNameLen"]*2'), 365 ('ProgName',':'), 366 ('MajorVer','<B'), 367 ('MinorVer','<B'), 368 ('BuildNumHi','<B'), 369 ('BuildNumLow','<B'), 370 ) 371 372class TDS_RETURNSTATUS(Structure): 373 structure = ( 374 ('TokenType','<B'), 375 ('Value','<L'), 376 ) 377 378class TDS_INFO_ERROR(Structure): 379 structure = ( 380 ('TokenType','<B'), 381 ('Length','<H'), 382 ('Number','<L'), 383 ('State','<B'), 384 ('Class','<B'), 385 ('MsgTextLen','<H'), 386 ('_MsgTextLen','_-MsgText','self["MsgTextLen"]*2'), 387 ('MsgText',':'), 388 ('ServerNameLen','<B'), 389 ('_ServerNameLen','_-ServerName','self["ServerNameLen"]*2'), 390 ('ServerName',':'), 391 ('ProcNameLen','<B'), 392 ('_ProcNameLen','_-ProcName','self["ProcNameLen"]*2'), 393 ('ProcName',':'), 394 ('LineNumber','<H'), 395 ) 396 397class TDS_ENVCHANGE(Structure): 398 structure = ( 399 ('TokenType','<B'), 400 ('Length','<H=4+len(Data)'), 401 ('Type','<B'), 402 ('_Data','_-Data','self["Length"]-1'), 403 ('Data',':'), 404 ) 405 406class TDS_DONEINPROC(Structure): 407 structure = ( 408 ('TokenType','<B'), 409 ('Status','<H'), 410 ('CurCmd','<H'), 411 ('DoneRowCount','<L'), 412 ) 413 414class TDS_ORDER(Structure): 415 structure = ( 416 ('TokenType','<B'), 417 ('Length','<H'), 418 ('_Data','_-Data','self["Length"]'), 419 ('Data',':'), 420 ) 421 422 423class TDS_ENVCHANGE_VARCHAR(Structure): 424 structure = ( 425 ('NewValueLen','<B=len(NewValue)'), 426 ('_NewValue','_-NewValue','self["NewValueLen"]*2'), 427 ('NewValue',':'), 428 ('OldValueLen','<B=len(OldValue)'), 429 ('_OldValue','_-OldValue','self["OldValueLen"]*2'), 430 ('OldValue',':'), 431 ) 432 433class TDS_ROW(Structure): 434 structure = ( 435 ('TokenType','<B'), 436 ('Data',':'), 437 ) 438 439class TDS_DONE(Structure): 440 structure = ( 441 ('TokenType','<B'), 442 ('Status','<H'), 443 ('CurCmd','<H'), 444 ('DoneRowCount','<L'), 445 ) 446 447class TDS_COLMETADATA(Structure): 448 structure = ( 449 ('TokenType','<B'), 450 ('Count','<H'), 451 ('Data',':'), 452 ) 453 454class MSSQL: 455 def __init__(self, address, port=1433, rowsPrinter=DummyPrint()): 456 #self.packetSize = 32764 457 self.packetSize = 32763 458 self.server = address 459 self.port = port 460 self.socket = 0 461 self.replies = {} 462 self.colMeta = [] 463 self.rows = [] 464 self.currentDB = '' 465 self.COL_SEPARATOR = ' ' 466 self.MAX_COL_LEN = 255 467 self.lastError = False 468 self.tlsSocket = None 469 self.__rowsPrinter = rowsPrinter 470 471 def getInstances(self, timeout = 5): 472 packet = SQLR() 473 packet['OpCode'] = SQLR_CLNT_UCAST_EX 474 475 # Open the connection 476 af, socktype, proto, canonname, sa = socket.getaddrinfo(self.server, SQLR_PORT, 0, socket.SOCK_DGRAM)[0] 477 s = socket.socket(af, socktype, proto) 478 479 s.sendto(str(packet), 0, ( self.server, SQLR_PORT )) 480 ready, _, _ = select.select([ s.fileno() ], [ ] , [ ], timeout) 481 if not ready: 482 return [] 483 else: 484 data, _ = s.recvfrom(65536, 0) 485 486 s.close() 487 resp = SQLR_Response(data) 488 489 # Now parse the results 490 entries = resp['Data'].split(';;') 491 492 # We don't want the last one, it's empty 493 entries.pop() 494 495 # the answer to send back 496 resp = [] 497 498 for i, entry in enumerate(entries): 499 fields = entry.split(';') 500 ret = {} 501 for j, field in enumerate(fields): 502 if (j & 0x1) == 0: 503 ret[field] = fields[j+1] 504 resp.append(ret) 505 506 return resp 507 508 509 def preLogin(self): 510 prelogin = TDS_PRELOGIN() 511 prelogin['Version'] = "\x08\x00\x01\x55\x00\x00" 512 #prelogin['Encryption'] = TDS_ENCRYPT_NOT_SUP 513 prelogin['Encryption'] = TDS_ENCRYPT_OFF 514 prelogin['ThreadID'] = struct.pack('<L',random.randint(0,65535)) 515 prelogin['Instance'] = 'MSSQLServer\x00' 516 517 self.sendTDS(TDS_PRE_LOGIN, str(prelogin), 0) 518 tds = self.recvTDS() 519 520 return TDS_PRELOGIN(tds['Data']) 521 522 def encryptPassword(self, password ): 523 524 return ''.join(map(lambda x: chr(((ord(x) & 0x0f) << 4) + ((ord(x) & 0xf0) >> 4) ^ 0xa5) , password)) 525 526 def connect(self): 527 af, socktype, proto, canonname, sa = socket.getaddrinfo(self.server, self.port, 0, socket.SOCK_STREAM)[0] 528 sock = socket.socket(af, socktype, proto) 529 530 try: 531 sock.connect(sa) 532 except Exception: 533 #import traceback 534 #traceback.print_exc() 535 raise 536 537 self.socket = sock 538 return sock 539 540 def disconnect(self): 541 if self.socket: 542 return self.socket.close() 543 544 def setPacketSize(self, packetSize): 545 self.packetSize = packetSize 546 547 def getPacketSize(self): 548 return self.packetSize 549 550 def socketSendall(self,data): 551 if self.tlsSocket is None: 552 return self.socket.sendall(data) 553 else: 554 self.tlsSocket.sendall(data) 555 dd = self.tlsSocket.bio_read(self.packetSize) 556 return self.socket.sendall(dd) 557 558 def sendTDS(self, packetType, data, packetID = 1): 559 if (len(data)-8) > self.packetSize: 560 remaining = data[self.packetSize-8:] 561 tds = TDSPacket() 562 tds['Type'] = packetType 563 tds['Status'] = TDS_STATUS_NORMAL 564 tds['PacketID'] = packetID 565 tds['Data'] = data[:self.packetSize-8] 566 self.socketSendall(str(tds)) 567 568 while len(remaining) > (self.packetSize-8): 569 packetID += 1 570 tds['PacketID'] = packetID 571 tds['Data'] = remaining[:self.packetSize-8] 572 self.socketSendall(str(tds)) 573 remaining = remaining[self.packetSize-8:] 574 data = remaining 575 packetID+=1 576 577 tds = TDSPacket() 578 tds['Type'] = packetType 579 tds['Status'] = TDS_STATUS_EOM 580 tds['PacketID'] = packetID 581 tds['Data'] = data 582 self.socketSendall(str(tds)) 583 584 def socketRecv(self, packetSize): 585 data = self.socket.recv(packetSize) 586 if self.tlsSocket is not None: 587 dd = '' 588 self.tlsSocket.bio_write(data) 589 while True: 590 try: 591 dd += self.tlsSocket.read(packetSize) 592 except SSL.WantReadError: 593 data2 = self.socket.recv(packetSize - len(data) ) 594 self.tlsSocket.bio_write(data2) 595 pass 596 else: 597 data = dd 598 break 599 return data 600 601 def recvTDS(self, packetSize = None): 602 # Do reassembly here 603 if packetSize is None: 604 packetSize = self.packetSize 605 packet = TDSPacket(self.socketRecv(packetSize)) 606 status = packet['Status'] 607 packetLen = packet['Length']-8 608 while packetLen > len(packet['Data']): 609 data = self.socketRecv(packetSize) 610 packet['Data'] += data 611 612 remaining = None 613 if packetLen < len(packet['Data']): 614 remaining = packet['Data'][packetLen:] 615 packet['Data'] = packet['Data'][:packetLen] 616 617 #print "REMAINING ", 618 #if remaining is None: 619 # print None 620 #else: 621 # print len(remaining) 622 623 while status != TDS_STATUS_EOM: 624 if remaining is not None: 625 tmpPacket = TDSPacket(remaining) 626 else: 627 tmpPacket = TDSPacket(self.socketRecv(packetSize)) 628 629 packetLen = tmpPacket['Length'] - 8 630 while packetLen > len(tmpPacket['Data']): 631 data = self.socketRecv(packetSize) 632 tmpPacket['Data'] += data 633 634 remaining = None 635 if packetLen < len(tmpPacket['Data']): 636 remaining = tmpPacket['Data'][packetLen:] 637 tmpPacket['Data'] = tmpPacket['Data'][:packetLen] 638 639 status = tmpPacket['Status'] 640 packet['Data'] += tmpPacket['Data'] 641 packet['Length'] += tmpPacket['Length'] - 8 642 643 #print packet['Length'] 644 return packet 645 646 def kerberosLogin(self, database, username, password='', domain='', hashes=None, aesKey='', kdcHost=None, TGT=None, TGS=None, useCache=True): 647 648 if hashes is not None: 649 lmhash, nthash = hashes.split(':') 650 lmhash = binascii.a2b_hex(lmhash) 651 nthash = binascii.a2b_hex(nthash) 652 else: 653 lmhash = '' 654 nthash = '' 655 656 resp = self.preLogin() 657 # Test this! 658 if resp['Encryption'] == TDS_ENCRYPT_REQ or resp['Encryption'] == TDS_ENCRYPT_OFF: 659 LOG.info("Encryption required, switching to TLS") 660 661 # Switching to TLS now 662 ctx = SSL.Context(SSL.TLSv1_METHOD) 663 ctx.set_cipher_list('RC4, AES256') 664 tls = SSL.Connection(ctx,None) 665 tls.set_connect_state() 666 while True: 667 try: 668 tls.do_handshake() 669 except SSL.WantReadError: 670 data = tls.bio_read(4096) 671 self.sendTDS(TDS_PRE_LOGIN, data,0) 672 tds = self.recvTDS() 673 tls.bio_write(tds['Data']) 674 else: 675 break 676 677 # SSL and TLS limitation: Secure Socket Layer (SSL) and its replacement, 678 # Transport Layer Security(TLS), limit data fragments to 16k in size. 679 self.packetSize = 16*1024-1 680 self.tlsSocket = tls 681 682 683 login = TDS_LOGIN() 684 685 login['HostName'] = (''.join([random.choice(string.letters) for _ in range(8)])).encode('utf-16le') 686 login['AppName'] = (''.join([random.choice(string.letters) for _ in range(8)])).encode('utf-16le') 687 login['ServerName'] = self.server.encode('utf-16le') 688 login['CltIntName'] = login['AppName'] 689 login['ClientPID'] = random.randint(0,1024) 690 login['PacketSize'] = self.packetSize 691 if database is not None: 692 login['Database'] = database.encode('utf-16le') 693 login['OptionFlags2'] = TDS_INIT_LANG_FATAL | TDS_ODBC_ON 694 695 from impacket.spnego import SPNEGO_NegTokenInit, TypesMech 696 # Importing down here so pyasn1 is not required if kerberos is not used. 697 from impacket.krb5.asn1 import AP_REQ, Authenticator, TGS_REP, seq_set 698 from impacket.krb5.kerberosv5 import getKerberosTGT, getKerberosTGS, KerberosError 699 from impacket.krb5 import constants 700 from impacket.krb5.types import Principal, KerberosTime, Ticket 701 from pyasn1.codec.der import decoder, encoder 702 from pyasn1.type.univ import noValue 703 from impacket.krb5.ccache import CCache 704 import os 705 import datetime 706 707 if useCache is True: 708 try: 709 ccache = CCache.loadFile(os.getenv('KRB5CCNAME')) 710 except: 711 # No cache present 712 pass 713 else: 714 # retrieve domain information from CCache file if needed 715 if domain == '': 716 domain = ccache.principal.realm['data'] 717 LOG.debug('Domain retrieved from CCache: %s' % domain) 718 719 LOG.debug("Using Kerberos Cache: %s" % os.getenv('KRB5CCNAME')) 720 principal = 'MSSQLSvc/%s.%s:%d@%s' % (self.server.split('.')[0], domain, self.port, domain.upper()) 721 creds = ccache.getCredential(principal) 722 723 if creds is not None: 724 TGS = creds.toTGS(principal) 725 LOG.debug('Using TGS from cache') 726 else: 727 # search for the port's instance name instead (instance name based SPN) 728 LOG.debug('Searching target\'s instances to look for port number %s' % self.port) 729 instances = self.getInstances() 730 instanceName = None 731 for i in instances: 732 try: 733 if string.atoi(i['tcp']) == self.port: 734 instanceName = i['InstanceName'] 735 except: 736 pass 737 738 if instanceName: 739 principal = 'MSSQLSvc/%s.%s:%s@%s' % (self.server, domain, instanceName, domain.upper()) 740 creds = ccache.getCredential(principal) 741 742 if creds is not None: 743 TGS = creds.toTGS(principal) 744 LOG.debug('Using TGS from cache') 745 else: 746 # Let's try for the TGT and go from there 747 principal = 'krbtgt/%s@%s' % (domain.upper(),domain.upper()) 748 creds = ccache.getCredential(principal) 749 if creds is not None: 750 TGT = creds.toTGT() 751 LOG.debug('Using TGT from cache') 752 else: 753 LOG.debug("No valid credentials found in cache. ") 754 755 # retrieve user information from CCache file if needed 756 if username == '' and creds is not None: 757 username = creds['client'].prettyPrint().split('@')[0] 758 LOG.debug('Username retrieved from CCache: %s' % username) 759 elif username == '' and len(ccache.principal.components) > 0: 760 username = ccache.principal.components[0]['data'] 761 LOG.debug('Username retrieved from CCache: %s' % username) 762 763 # First of all, we need to get a TGT for the user 764 userName = Principal(username, type=constants.PrincipalNameType.NT_PRINCIPAL.value) 765 while True: 766 if TGT is None: 767 if TGS is None: 768 try: 769 tgt, cipher, oldSessionKey, sessionKey = getKerberosTGT(userName, password, domain, lmhash, nthash, aesKey, kdcHost) 770 except KerberosError, e: 771 if e.getErrorCode() == constants.ErrorCodes.KDC_ERR_ETYPE_NOSUPP.value: 772 # We might face this if the target does not support AES 773 # So, if that's the case we'll force using RC4 by converting 774 # the password to lm/nt hashes and hope for the best. If that's already 775 # done, byebye. 776 if lmhash is '' and nthash is '' and (aesKey is '' or aesKey is None) and TGT is None and TGS is None: 777 from impacket.ntlm import compute_lmhash, compute_nthash 778 LOG.debug('Got KDC_ERR_ETYPE_NOSUPP, fallback to RC4') 779 lmhash = compute_lmhash(password) 780 nthash = compute_nthash(password) 781 continue 782 else: 783 raise 784 else: 785 raise 786 else: 787 tgt = TGT['KDC_REP'] 788 cipher = TGT['cipher'] 789 sessionKey = TGT['sessionKey'] 790 791 if TGS is None: 792 # From https://msdn.microsoft.com/en-us/library/ms191153.aspx?f=255&MSPPError=-2147217396 793 # Beginning with SQL Server 2008, the SPN format is changed in order to support Kerberos authentication 794 # on TCP/IP, named pipes, and shared memory. The supported SPN formats for named and default instances 795 # are as follows. 796 # Named instance 797 # MSSQLSvc/FQDN:[port | instancename], where: 798 # MSSQLSvc is the service that is being registered. 799 # FQDN is the fully qualified domain name of the server. 800 # port is the TCP port number. 801 # instancename is the name of the SQL Server instance. 802 serverName = Principal('MSSQLSvc/%s.%s:%d' % (self.server.split('.')[0], domain, self.port), type=constants.PrincipalNameType.NT_SRV_INST.value) 803 try: 804 tgs, cipher, oldSessionKey, sessionKey = getKerberosTGS(serverName, domain, kdcHost, tgt, cipher, sessionKey) 805 except KerberosError, e: 806 if e.getErrorCode() == constants.ErrorCodes.KDC_ERR_ETYPE_NOSUPP.value: 807 # We might face this if the target does not support AES 808 # So, if that's the case we'll force using RC4 by converting 809 # the password to lm/nt hashes and hope for the best. If that's already 810 # done, byebye. 811 if lmhash is '' and nthash is '' and (aesKey is '' or aesKey is None) and TGT is None and TGS is None: 812 from impacket.ntlm import compute_lmhash, compute_nthash 813 LOG.debug('Got KDC_ERR_ETYPE_NOSUPP, fallback to RC4') 814 lmhash = compute_lmhash(password) 815 nthash = compute_nthash(password) 816 else: 817 raise 818 else: 819 raise 820 else: 821 break 822 else: 823 tgs = TGS['KDC_REP'] 824 cipher = TGS['cipher'] 825 sessionKey = TGS['sessionKey'] 826 break 827 828 # Let's build a NegTokenInit with a Kerberos REQ_AP 829 830 blob = SPNEGO_NegTokenInit() 831 832 # Kerberos 833 blob['MechTypes'] = [TypesMech['MS KRB5 - Microsoft Kerberos 5']] 834 835 # Let's extract the ticket from the TGS 836 tgs = decoder.decode(tgs, asn1Spec = TGS_REP())[0] 837 ticket = Ticket() 838 ticket.from_asn1(tgs['ticket']) 839 840 # Now let's build the AP_REQ 841 apReq = AP_REQ() 842 apReq['pvno'] = 5 843 apReq['msg-type'] = int(constants.ApplicationTagNumbers.AP_REQ.value) 844 845 opts = list() 846 apReq['ap-options'] = constants.encodeFlags(opts) 847 seq_set(apReq,'ticket', ticket.to_asn1) 848 849 authenticator = Authenticator() 850 authenticator['authenticator-vno'] = 5 851 authenticator['crealm'] = domain 852 seq_set(authenticator, 'cname', userName.components_to_asn1) 853 now = datetime.datetime.utcnow() 854 855 authenticator['cusec'] = now.microsecond 856 authenticator['ctime'] = KerberosTime.to_asn1(now) 857 858 encodedAuthenticator = encoder.encode(authenticator) 859 860 # Key Usage 11 861 # AP-REQ Authenticator (includes application authenticator 862 # subkey), encrypted with the application session key 863 # (Section 5.5.1) 864 encryptedEncodedAuthenticator = cipher.encrypt(sessionKey, 11, encodedAuthenticator, None) 865 866 apReq['authenticator'] = noValue 867 apReq['authenticator']['etype'] = cipher.enctype 868 apReq['authenticator']['cipher'] = encryptedEncodedAuthenticator 869 870 blob['MechToken'] = encoder.encode(apReq) 871 872 login['OptionFlags2'] |= TDS_INTEGRATED_SECURITY_ON 873 874 login['SSPI'] = blob.getData() 875 login['Length'] = len(str(login)) 876 877 # Send the NTLMSSP Negotiate or SQL Auth Packet 878 self.sendTDS(TDS_LOGIN7, str(login)) 879 880 # According to the specs, if encryption is not required, we must encrypt just 881 # the first Login packet :-o 882 if resp['Encryption'] == TDS_ENCRYPT_OFF: 883 self.tlsSocket = None 884 885 tds = self.recvTDS() 886 887 self.replies = self.parseReply(tds['Data']) 888 889 if self.replies.has_key(TDS_LOGINACK_TOKEN): 890 return True 891 else: 892 return False 893 894 def login(self, database, username, password='', domain='', hashes = None, useWindowsAuth = False): 895 896 if hashes is not None: 897 lmhash, nthash = hashes.split(':') 898 lmhash = binascii.a2b_hex(lmhash) 899 nthash = binascii.a2b_hex(nthash) 900 else: 901 lmhash = '' 902 nthash = '' 903 904 resp = self.preLogin() 905 # Test this! 906 if resp['Encryption'] == TDS_ENCRYPT_REQ or resp['Encryption'] == TDS_ENCRYPT_OFF: 907 LOG.info("Encryption required, switching to TLS") 908 909 # Switching to TLS now 910 ctx = SSL.Context(SSL.TLSv1_METHOD) 911 ctx.set_cipher_list('RC4, AES256') 912 tls = SSL.Connection(ctx,None) 913 tls.set_connect_state() 914 while True: 915 try: 916 tls.do_handshake() 917 except SSL.WantReadError: 918 data = tls.bio_read(4096) 919 self.sendTDS(TDS_PRE_LOGIN, data,0) 920 tds = self.recvTDS() 921 tls.bio_write(tds['Data']) 922 else: 923 break 924 925 # SSL and TLS limitation: Secure Socket Layer (SSL) and its replacement, 926 # Transport Layer Security(TLS), limit data fragments to 16k in size. 927 self.packetSize = 16*1024-1 928 self.tlsSocket = tls 929 930 931 login = TDS_LOGIN() 932 933 login['HostName'] = (''.join([random.choice(string.letters) for i in range(8)])).encode('utf-16le') 934 login['AppName'] = (''.join([random.choice(string.letters) for i in range(8)])).encode('utf-16le') 935 login['ServerName'] = self.server.encode('utf-16le') 936 login['CltIntName'] = login['AppName'] 937 login['ClientPID'] = random.randint(0,1024) 938 login['PacketSize'] = self.packetSize 939 if database is not None: 940 login['Database'] = database.encode('utf-16le') 941 login['OptionFlags2'] = TDS_INIT_LANG_FATAL | TDS_ODBC_ON 942 943 if useWindowsAuth is True: 944 login['OptionFlags2'] |= TDS_INTEGRATED_SECURITY_ON 945 # NTLMSSP Negotiate 946 auth = ntlm.getNTLMSSPType1('','') 947 login['SSPI'] = str(auth) 948 else: 949 login['UserName'] = username.encode('utf-16le') 950 login['Password'] = self.encryptPassword(password.encode('utf-16le')) 951 login['SSPI'] = '' 952 953 login['Length'] = len(str(login)) 954 955 # Send the NTLMSSP Negotiate or SQL Auth Packet 956 self.sendTDS(TDS_LOGIN7, str(login)) 957 958 # According to the specs, if encryption is not required, we must encrypt just 959 # the first Login packet :-o 960 if resp['Encryption'] == TDS_ENCRYPT_OFF: 961 self.tlsSocket = None 962 963 tds = self.recvTDS() 964 965 966 if useWindowsAuth is True: 967 serverChallenge = tds['Data'][3:] 968 969 # Generate the NTLM ChallengeResponse AUTH 970 type3, exportedSessionKey = ntlm.getNTLMSSPType3(auth, serverChallenge, username, password, domain, lmhash, nthash) 971 972 self.sendTDS(TDS_SSPI, str(type3)) 973 tds = self.recvTDS() 974 975 self.replies = self.parseReply(tds['Data']) 976 977 if self.replies.has_key(TDS_LOGINACK_TOKEN): 978 return True 979 else: 980 return False 981 982 983 def processColMeta(self): 984 for col in self.colMeta: 985 if col['Type'] in [TDS_NVARCHARTYPE, TDS_NCHARTYPE, TDS_NTEXTTYPE]: 986 col['Length'] = col['TypeData']/2 987 fmt = '%%-%ds' 988 elif col['Type'] in [TDS_GUIDTYPE]: 989 col['Length'] = 36 990 fmt = '%%%ds' 991 elif col['Type'] in [TDS_DECIMALNTYPE,TDS_NUMERICNTYPE]: 992 col['Length'] = ord(col['TypeData'][0]) 993 fmt = '%%%ds' 994 elif col['Type'] in [TDS_DATETIMNTYPE]: 995 col['Length'] = 19 996 fmt = '%%-%ds' 997 elif col['Type'] in [TDS_INT4TYPE, TDS_INTNTYPE]: 998 col['Length'] = 11 999 fmt = '%%%ds' 1000 elif col['Type'] in [TDS_FLTNTYPE, TDS_MONEYNTYPE]: 1001 col['Length'] = 25 1002 fmt = '%%%ds' 1003 elif col['Type'] in [TDS_BITNTYPE, TDS_BIGCHARTYPE]: 1004 col['Length'] = col['TypeData'] 1005 fmt = '%%%ds' 1006 elif col['Type'] in [TDS_BIGBINARYTYPE, TDS_BIGVARBINTYPE]: 1007 col['Length'] = col['TypeData'] * 2 1008 fmt = '%%%ds' 1009 elif col['Type'] in [TDS_TEXTTYPE, TDS_BIGVARCHRTYPE]: 1010 col['Length'] = col['TypeData'] 1011 fmt = '%%-%ds' 1012 else: 1013 col['Length'] = 10 1014 fmt = '%%%ds' 1015 1016 if len(col['Name']) > col['Length']: 1017 col['Length'] = len(col['Name']) 1018 elif col['Length'] > self.MAX_COL_LEN: 1019 col['Length'] = self.MAX_COL_LEN 1020 1021 col['Format'] = fmt % col['Length'] 1022 1023 1024 def printColumnsHeader(self): 1025 if len(self.colMeta) == 0: 1026 return 1027 for col in self.colMeta: 1028 self.__rowsPrinter.logMessage(col['Format'] % col['Name'] + self.COL_SEPARATOR + '\n') 1029 for col in self.colMeta: 1030 self.__rowsPrinter.logMessage('-'*col['Length'] + self.COL_SEPARATOR + '\n') 1031 1032 1033 def printRows(self): 1034 if self.lastError is True: 1035 return 1036 self.processColMeta() 1037 self.printColumnsHeader() 1038 for row in self.rows: 1039 for col in self.colMeta: 1040 self.__rowsPrinter.logMessage(col['Format'] % row[col['Name']] + self.COL_SEPARATOR) 1041 1042 def printReplies(self): 1043 for keys in self.replies.keys(): 1044 for i, key in enumerate(self.replies[keys]): 1045 if key['TokenType'] == TDS_ERROR_TOKEN: 1046 error = "ERROR(%s): Line %d: %s" % (key['ServerName'].decode('utf-16le'), key['LineNumber'], key['MsgText'].decode('utf-16le')) 1047 self.lastError = SQLErrorException("ERROR: Line %d: %s" % (key['LineNumber'], key['MsgText'].decode('utf-16le'))) 1048 LOG.error(error) 1049 1050 elif key['TokenType'] == TDS_INFO_TOKEN: 1051 LOG.info("INFO(%s): Line %d: %s" % (key['ServerName'].decode('utf-16le'), key['LineNumber'], key['MsgText'].decode('utf-16le'))) 1052 1053 elif key['TokenType'] == TDS_LOGINACK_TOKEN: 1054 LOG.info("ACK: Result: %s - %s (%d%d %d%d) " % (key['Interface'], key['ProgName'].decode('utf-16le'), key['MajorVer'], key['MinorVer'], key['BuildNumHi'], key['BuildNumLow'])) 1055 1056 elif key['TokenType'] == TDS_ENVCHANGE_TOKEN: 1057 if key['Type'] in (TDS_ENVCHANGE_DATABASE, TDS_ENVCHANGE_LANGUAGE, TDS_ENVCHANGE_CHARSET, TDS_ENVCHANGE_PACKETSIZE): 1058 record = TDS_ENVCHANGE_VARCHAR(key['Data']) 1059 if record['OldValue'] == '': 1060 record['OldValue'] = 'None'.encode('utf-16le') 1061 elif record['NewValue'] == '': 1062 record['NewValue'] = 'None'.encode('utf-16le') 1063 if key['Type'] == TDS_ENVCHANGE_DATABASE: 1064 _type = 'DATABASE' 1065 elif key['Type'] == TDS_ENVCHANGE_LANGUAGE: 1066 _type = 'LANGUAGE' 1067 elif key['Type'] == TDS_ENVCHANGE_CHARSET: 1068 _type = 'CHARSET' 1069 elif key['Type'] == TDS_ENVCHANGE_PACKETSIZE: 1070 _type = 'PACKETSIZE' 1071 else: 1072 _type = "%d" % key['Type'] 1073 LOG.info("ENVCHANGE(%s): Old Value: %s, New Value: %s" % (_type,record['OldValue'].decode('utf-16le'), record['NewValue'].decode('utf-16le'))) 1074 1075 def parseRow(self,token,tuplemode=False): 1076 # TODO: This REALLY needs to be improved. Right now we don't support correctly all the data types 1077 # help would be appreciated ;) 1078 if len(token) == 1: 1079 return 0 1080 1081 row = [] if tuplemode else {} 1082 1083 origDataLen = len(token['Data']) 1084 data = token['Data'] 1085 for col in self.colMeta: 1086 _type = col['Type'] 1087 if (_type == TDS_NVARCHARTYPE) |\ 1088 (_type == TDS_NCHARTYPE): 1089 #print "NVAR 0x%x" % _type 1090 charLen = struct.unpack('<H',data[:struct.calcsize('<H')])[0] 1091 data = data[struct.calcsize('<H'):] 1092 if charLen != 0xFFFF: 1093 value = data[:charLen].decode('utf-16le') 1094 data = data[charLen:] 1095 else: 1096 value = 'NULL' 1097 1098 elif _type == TDS_BIGVARCHRTYPE: 1099 charLen = struct.unpack('<H',data[:struct.calcsize('<H')])[0] 1100 data = data[struct.calcsize('<H'):] 1101 if charLen != 0xFFFF: 1102 value = data[:charLen] 1103 data = data[charLen:] 1104 else: 1105 value = 'NULL' 1106 1107 elif _type == TDS_GUIDTYPE: 1108 uuidLen = ord(data[0]) 1109 data = data[1:] 1110 if uuidLen > 0: 1111 uu = data[:uuidLen] 1112 value = uuid.bin_to_string(uu) 1113 data = data[uuidLen:] 1114 else: 1115 value = 'NULL' 1116 1117 elif (_type == TDS_NTEXTTYPE) |\ 1118 (_type == TDS_IMAGETYPE) : 1119 # Skip the pointer data 1120 charLen = ord(data[0]) 1121 if charLen == 0: 1122 value = 'NULL' 1123 data = data[1:] 1124 else: 1125 data = data[1+charLen+8:] 1126 charLen = struct.unpack('<L',data[:struct.calcsize('<L')])[0] 1127 data = data[struct.calcsize('<L'):] 1128 if charLen != 0xFFFF: 1129 if _type == TDS_NTEXTTYPE: 1130 value = data[:charLen].decode('utf-16le') 1131 else: 1132 value = binascii.b2a_hex(data[:charLen]) 1133 data = data[charLen:] 1134 else: 1135 value = 'NULL' 1136 1137 elif _type == TDS_TEXTTYPE: 1138 # Skip the pointer data 1139 charLen = ord(data[0]) 1140 if charLen == 0: 1141 value = 'NULL' 1142 data = data[1:] 1143 else: 1144 data = data[1+charLen+8:] 1145 charLen = struct.unpack('<L',data[:struct.calcsize('<L')])[0] 1146 data = data[struct.calcsize('<L'):] 1147 if charLen != 0xFFFF: 1148 value = data[:charLen] 1149 data = data[charLen:] 1150 else: 1151 value = 'NULL' 1152 1153 elif (_type == TDS_BIGVARBINTYPE) |\ 1154 (_type == TDS_BIGBINARYTYPE): 1155 charLen = struct.unpack('<H',data[:struct.calcsize('<H')])[0] 1156 data = data[struct.calcsize('<H'):] 1157 if charLen != 0xFFFF: 1158 value = binascii.b2a_hex(data[:charLen]) 1159 data = data[charLen:] 1160 else: 1161 value = 'NULL' 1162 1163 elif (_type == TDS_DATETIM4TYPE) |\ 1164 (_type == TDS_DATETIMNTYPE) |\ 1165 (_type == TDS_DATETIMETYPE): 1166 value = '' 1167 if _type == TDS_DATETIMNTYPE: 1168 # For DATETIMNTYPE, the only valid lengths are 0x04 and 0x08, which map to smalldatetime and 1169 # datetime SQL data _types respectively. 1170 if ord(data[0]) == 4: 1171 _type = TDS_DATETIM4TYPE 1172 elif ord(data[0]) == 8: 1173 _type = TDS_DATETIMETYPE 1174 else: 1175 value = 'NULL' 1176 data = data[1:] 1177 if _type == TDS_DATETIMETYPE: 1178 # datetime is represented in the following sequence: 1179 # * One 4-byte signed integer that represents the number of days since January 1, 1900. Negative 1180 # numbers are allowed to represents dates since January 1, 1753. 1181 # * One 4-byte unsigned integer that represents the number of one three-hundredths of a second 1182 # (300 counts per second) elapsed since 12 AM that day. 1183 dateValue = struct.unpack('<l',data[:4])[0] 1184 data = data[4:] 1185 if dateValue < 0: 1186 baseDate = datetime.date(1753,1,1) 1187 else: 1188 baseDate = datetime.date(1900,1,1) 1189 timeValue = struct.unpack('<L',data[:4])[0] 1190 data = data[4:] 1191 elif _type == TDS_DATETIM4TYPE: 1192 # Small datetime 1193 # 2.2.5.5.1.8 1194 # Date/Times 1195 # smalldatetime is represented in the following sequence: 1196 # * One 2-byte unsigned integer that represents the number of days since January 1, 1900. 1197 # * One 2-byte unsigned integer that represents the number of minutes elapsed since 12 AM that 1198 # day. 1199 dateValue = struct.unpack('<H',data[:struct.calcsize('<H')])[0] 1200 data = data[struct.calcsize('<H'):] 1201 timeValue = struct.unpack('<H',data[:struct.calcsize('<H')])[0] 1202 data = data[struct.calcsize('<H'):] 1203 baseDate = datetime.date(1900,1,1) 1204 if value != 'NULL': 1205 dateValue = datetime.date.fromordinal(baseDate.toordinal() + dateValue) 1206 hours, mod = divmod(timeValue/300, 60*60) 1207 minutes, second = divmod(mod, 60) 1208 value = datetime.datetime(dateValue.year, dateValue.month, dateValue.day, hours, minutes, second) 1209 1210 elif (_type == TDS_INT4TYPE) |\ 1211 (_type == TDS_MONEY4TYPE) |\ 1212 (_type == TDS_FLT4TYPE): 1213 #print "INT4" 1214 value = struct.unpack('<l',data[:struct.calcsize('<l')])[0] 1215 data = data[struct.calcsize('<l'):] 1216 1217 elif _type == TDS_FLTNTYPE: 1218 valueSize = ord(data[:1]) 1219 if valueSize == 4: 1220 fmt = '<f' 1221 elif valueSize == 8: 1222 fmt = '<d' 1223 1224 data = data[1:] 1225 1226 if valueSize > 0: 1227 value = struct.unpack(fmt,data[:valueSize])[0] 1228 data = data[valueSize:] 1229 else: 1230 value = 'NULL' 1231 1232 elif _type == TDS_MONEYNTYPE: 1233 valueSize = ord(data[:1]) 1234 if valueSize == 4: 1235 fmt = '<l' 1236 elif valueSize == 8: 1237 fmt = '<q' 1238 1239 data = data[1:] 1240 1241 if valueSize > 0: 1242 value = struct.unpack(fmt,data[:valueSize])[0] 1243 if valueSize == 4: 1244 value = float(value) / math.pow(10,4) 1245 else: 1246 value = float(value >> 32) / math.pow(10,4) 1247 data = data[valueSize:] 1248 else: 1249 value = 'NULL' 1250 1251 1252 elif _type == TDS_BIGCHARTYPE: 1253 #print "BIGC" 1254 charLen = struct.unpack('<H',data[:struct.calcsize('<H')])[0] 1255 data = data[struct.calcsize('<H'):] 1256 value = data[:charLen] 1257 data = data[charLen:] 1258 1259 elif (_type == TDS_INT8TYPE) |\ 1260 (_type == TDS_FLT8TYPE) |\ 1261 (_type == TDS_MONEYTYPE): 1262 #print "DATETIME" 1263 value = struct.unpack('<q',data[:struct.calcsize('<q')])[0] 1264 data = data[struct.calcsize('<q'):] 1265 1266 1267 elif _type == TDS_INT2TYPE: 1268 #print "INT2TYPE" 1269 value = struct.unpack('<H',(data[:2]))[0] 1270 data = data[2:] 1271 1272 elif _type == TDS_DATENTYPE: 1273 # date is represented as one 3-byte unsigned integer that represents the number of days since 1274 # January 1, year 1. 1275 valueSize = ord(data[:1]) 1276 data = data[1:] 1277 if valueSize > 0: 1278 dateBytes = data[:valueSize] 1279 dateValue = struct.unpack('<L','\x00'+dateBytes)[0] 1280 value = datetime.date.fromtimestamp(dateValue) 1281 data = data[valueSize:] 1282 else: 1283 value = 'NULL' 1284 1285 elif (_type == TDS_BITTYPE) |\ 1286 (_type == TDS_INT1TYPE): 1287 #print "BITTYPE" 1288 value = ord(data[:1]) 1289 data = data[1:] 1290 1291 elif (_type == TDS_NUMERICNTYPE) |\ 1292 (_type == TDS_DECIMALNTYPE): 1293 valueLen = ord(data[:1]) 1294 data = data[1:] 1295 value = data[:valueLen] 1296 data = data[valueLen:] 1297 precision = ord(col['TypeData'][1]) 1298 scale = ord(col['TypeData'][2]) 1299 if valueLen > 0: 1300 isPositiveSign = ord(value[0]) 1301 if (valueLen-1) == 2: 1302 fmt = '<H' 1303 elif (valueLen-1) == 4: 1304 fmt = '<L' 1305 elif (valueLen-1) == 8: 1306 fmt = '<Q' 1307 else: 1308 # Still don't know how to handle higher values 1309 value = "TODO: Interpret TDS_NUMERICNTYPE correctly" 1310 number = struct.unpack(fmt, value[1:])[0] 1311 number /= math.pow(precision, scale) 1312 if isPositiveSign == 0: 1313 number *= -1 1314 value = number 1315 else: 1316 value = 'NULL' 1317 1318 elif _type == TDS_BITNTYPE: 1319 #print "BITNTYPE" 1320 valueSize = ord(data[:1]) 1321 data = data[1:] 1322 if valueSize > 0: 1323 if valueSize == 1: 1324 value = ord(data[:valueSize]) 1325 else: 1326 value = data[:valueSize] 1327 else: 1328 value = 'NULL' 1329 data = data[valueSize:] 1330 1331 elif _type == TDS_INTNTYPE: 1332 valueSize = ord(data[:1]) 1333 if valueSize == 1: 1334 fmt = '<B' 1335 elif valueSize == 2: 1336 fmt = '<h' 1337 elif valueSize == 4: 1338 fmt = '<l' 1339 elif valueSize == 8: 1340 fmt = '<q' 1341 else: 1342 fmt = '' 1343 1344 data = data[1:] 1345 1346 if valueSize > 0: 1347 value = struct.unpack(fmt,data[:valueSize])[0] 1348 data = data[valueSize:] 1349 else: 1350 value = 'NULL' 1351 elif _type == TDS_SSVARIANTTYPE: 1352 raise Exception("ParseRow: SQL Variant type not yet supported :(") 1353 else: 1354 raise Exception("ParseROW: Unsupported data type: 0%x" % _type) 1355 1356 if tuplemode: 1357 row.append(value) 1358 else: 1359 row[col['Name']] = value 1360 1361 1362 self.rows.append(row) 1363 1364 return origDataLen - len(data) 1365 1366 def parseColMetaData(self, token): 1367 # TODO Add support for more data types! 1368 count = token['Count'] 1369 if count == 0xFFFF: 1370 return 0 1371 1372 self.colMeta = [] 1373 origDataLen = len(token['Data']) 1374 data = token['Data'] 1375 for i in range(count): 1376 column = {} 1377 userType = struct.unpack('<H',data[:struct.calcsize('<H')])[0] 1378 data = data[struct.calcsize('<H'):] 1379 flags = struct.unpack('<H',data[:struct.calcsize('<H')])[0] 1380 data = data[struct.calcsize('<H'):] 1381 colType = struct.unpack('<B',data[:struct.calcsize('<B')])[0] 1382 data = data[struct.calcsize('<B'):] 1383 if (colType == TDS_BITTYPE) |\ 1384 (colType == TDS_INT1TYPE) |\ 1385 (colType == TDS_INT2TYPE) |\ 1386 (colType == TDS_INT8TYPE) |\ 1387 (colType == TDS_DATETIMETYPE) |\ 1388 (colType == TDS_DATETIM4TYPE) |\ 1389 (colType == TDS_FLT4TYPE) |\ 1390 (colType == TDS_FLT8TYPE) |\ 1391 (colType == TDS_MONEYTYPE) |\ 1392 (colType == TDS_MONEY4TYPE) |\ 1393 (colType == TDS_DATENTYPE) |\ 1394 (colType == TDS_INT4TYPE): 1395 typeData = '' 1396 elif (colType == TDS_INTNTYPE) |\ 1397 (colType == TDS_TIMENTYPE) |\ 1398 (colType == TDS_DATETIME2NTYPE) |\ 1399 (colType == TDS_DATETIMEOFFSETNTYPE) |\ 1400 (colType == TDS_FLTNTYPE) |\ 1401 (colType == TDS_MONEYNTYPE) |\ 1402 (colType == TDS_GUIDTYPE) |\ 1403 (colType == TDS_BITNTYPE): 1404 typeData = ord(data[0]) 1405 data = data[1:] 1406 1407 elif colType == TDS_DATETIMNTYPE: 1408 # For DATETIMNTYPE, the only valid lengths are 0x04 and 0x08, which map to smalldatetime and 1409 # datetime SQL data types respectively. 1410 typeData = ord(data[0]) 1411 data = data[1:] 1412 1413 elif (colType == TDS_BIGVARBINTYPE) |\ 1414 (colType == TDS_BIGBINARYTYPE) |\ 1415 (colType == TDS_NCHARTYPE) |\ 1416 (colType == TDS_NVARCHARTYPE) |\ 1417 (colType == TDS_BIGVARCHRTYPE) |\ 1418 (colType == TDS_BIGCHARTYPE): 1419 typeData = struct.unpack('<H',data[:2])[0] 1420 data = data[2:] 1421 elif (colType == TDS_DECIMALNTYPE) |\ 1422 (colType == TDS_NUMERICNTYPE) |\ 1423 (colType == TDS_DECIMALTYPE): 1424 typeData = data[:3] 1425 data = data[3:] 1426 elif (colType == TDS_IMAGETYPE) |\ 1427 (colType == TDS_TEXTTYPE) |\ 1428 (colType == TDS_XMLTYPE) |\ 1429 (colType == TDS_SSVARIANTTYPE) |\ 1430 (colType == TDS_NTEXTTYPE): 1431 typeData = struct.unpack('<L',data[:4])[0] 1432 data = data[4:] 1433 else: 1434 raise Exception("Unsupported data type: 0x%x" % colType) 1435 1436 # Collation exceptions: 1437 if (colType == TDS_NTEXTTYPE) |\ 1438 (colType == TDS_BIGCHARTYPE) |\ 1439 (colType == TDS_BIGVARCHRTYPE) |\ 1440 (colType == TDS_NCHARTYPE) |\ 1441 (colType == TDS_NVARCHARTYPE) |\ 1442 (colType == TDS_TEXTTYPE): 1443 # Skip collation 1444 data = data[5:] 1445 1446 # PartTableName exceptions: 1447 if (colType == TDS_IMAGETYPE) |\ 1448 (colType == TDS_TEXTTYPE) |\ 1449 (colType == TDS_NTEXTTYPE): 1450 # This types have Table Elements, we just discard them for now. 1451 # ToDo parse this correctly! 1452 # Get the Length 1453 dataLen = struct.unpack('<H',data[:2])[0] 1454 data = data[2:] 1455 # skip the text 1456 data = data[dataLen*2:] 1457 1458 colNameLength = struct.unpack('<B',data[:struct.calcsize('<B')])[0] 1459 data = data[struct.calcsize('<B'):] 1460 colName = data[:colNameLength*2].decode('utf-16le') 1461 data = data[colNameLength*2:] 1462 column['Name'] = colName 1463 column['Type'] = colType 1464 column['TypeData'] = typeData 1465 column['Flags'] = flags 1466 self.colMeta.append(column) 1467 1468 return origDataLen - len(data) 1469 1470 def parseReply(self, tokens,tuplemode=False): 1471 if len(tokens) == 0: 1472 return False 1473 1474 replies = {} 1475 while len(tokens) > 0: 1476 tokenID = struct.unpack('B',tokens[0])[0] 1477 if tokenID == TDS_ERROR_TOKEN: 1478 token = TDS_INFO_ERROR(tokens) 1479 elif tokenID == TDS_RETURNSTATUS_TOKEN: 1480 token = TDS_RETURNSTATUS(tokens) 1481 elif tokenID == TDS_INFO_TOKEN: 1482 token = TDS_INFO_ERROR(tokens) 1483 elif tokenID == TDS_LOGINACK_TOKEN: 1484 token = TDS_LOGIN_ACK(tokens) 1485 elif tokenID == TDS_ENVCHANGE_TOKEN: 1486 token = TDS_ENVCHANGE(tokens) 1487 if token['Type'] is TDS_ENVCHANGE_PACKETSIZE: 1488 record = TDS_ENVCHANGE_VARCHAR(token['Data']) 1489 self.packetSize = string.atoi( record['NewValue'].decode('utf-16le') ) 1490 elif token['Type'] is TDS_ENVCHANGE_DATABASE: 1491 record = TDS_ENVCHANGE_VARCHAR(token['Data']) 1492 self.currentDB = record['NewValue'].decode('utf-16le') 1493 1494 elif (tokenID == TDS_DONEINPROC_TOKEN) |\ 1495 (tokenID == TDS_DONEPROC_TOKEN): 1496 token = TDS_DONEINPROC(tokens) 1497 elif tokenID == TDS_ORDER_TOKEN: 1498 token = TDS_ORDER(tokens) 1499 elif tokenID == TDS_ROW_TOKEN: 1500 #print "ROW" 1501 token = TDS_ROW(tokens) 1502 tokenLen = self.parseRow(token,tuplemode) 1503 token['Data'] = token['Data'][:tokenLen] 1504 elif tokenID == TDS_COLMETADATA_TOKEN: 1505 #print "COLMETA" 1506 token = TDS_COLMETADATA(tokens) 1507 tokenLen = self.parseColMetaData(token) 1508 token['Data'] = token['Data'][:tokenLen] 1509 elif tokenID == TDS_DONE_TOKEN: 1510 token = TDS_DONE(tokens) 1511 else: 1512 LOG.error("Unknown Token %x" % tokenID) 1513 return replies 1514 1515 if replies.has_key(tokenID) is not True: 1516 replies[tokenID] = list() 1517 1518 replies[tokenID].append(token) 1519 tokens = tokens[len(token):] 1520 #print "TYPE 0x%x, LEN: %d" %(tokenID, len(token)) 1521 #print repr(tokens[:10]) 1522 1523 return replies 1524 1525 def batch(self, cmd,tuplemode=False,wait=True): 1526 # First of all we clear the rows, colMeta and lastError 1527 self.rows = [] 1528 self.colMeta = [] 1529 self.lastError = False 1530 self.sendTDS(TDS_SQL_BATCH, (cmd+'\r\n').encode('utf-16le')) 1531 if wait: 1532 tds = self.recvTDS() 1533 self.replies = self.parseReply(tds['Data'],tuplemode) 1534 return self.rows 1535 else: 1536 return True 1537 1538 1539 def batchStatement(self, cmd,tuplemode=False): 1540 # First of all we clear the rows, colMeta and lastError 1541 self.rows = [] 1542 self.colMeta = [] 1543 self.lastError = False 1544 self.sendTDS(TDS_SQL_BATCH, (cmd+'\r\n').encode('utf-16le')) 1545 #self.recvTDS() 1546 1547 1548 # Handy alias 1549 sql_query = batch 1550 1551 def changeDB(self, db): 1552 if db != self.currentDB: 1553 chdb = 'use %s' % db 1554 self.batch(chdb) 1555 self.printReplies() 1556 1557 def RunSQLQuery(self,db,sql_query,tuplemode=False,wait=True,**kwArgs): 1558 db = db or 'master' 1559 self.changeDB(db) 1560 self.printReplies() 1561 ret = self.batch(sql_query,tuplemode,wait) 1562 if wait: 1563 self.printReplies() 1564 if self.lastError: 1565 raise self.lastError 1566 if self.lastError: 1567 raise self.lastError 1568 return ret 1569 1570 def RunSQLStatement(self,db,sql_query,wait=True,**kwArgs): 1571 self.RunSQLQuery(db,sql_query,wait=wait) 1572 if self.lastError: 1573 raise self.lastError 1574 return True 1575