-- MySQL Server information script
-- rev 0.3 (1-09-2008)

id = "MySQL"

description = "Attempts to extract database information from MySQL server"

author = "Thomas Buchanan <tbuchanan@thecompassgrp.net>"

license = "See nmaps COPYING for licence"

-- if you feel this is safe for your network, feel free to change it
categories = {"intrusive"}

require("stdnse")
require("shortport")
require("strbuf")
require("bit")
require("listop")
if nmap.have_ssl() then
	require("openssl")
end

portrule = shortport.portnumber(3306, "tcp")

-- attempts to login to service with the given username and no password
-- if login is successful, returns a socket with an open connection to the server
-- if login is unsuccessful, returns nil
function try_login_np(host, port, username)

	local payload = strbuf.new()

	local pay_len = string.len(username) + 34
	payload = payload .. string.char(pay_len) .. "\000\000\001\133\166\003\000"
	payload = payload .. "\000\000\000\001\008\000\000\000"
	payload = payload .. "\000\000\000\000\000\000\000\000"
	payload = payload .. "\000\000\000\000\000\000\000\000"
	payload = payload .. "\000\000\000\000" .. username
	payload = payload .. "\000\000"
		
	-- create the socket used for our connection, set a reasonable timeout
	local socket = nmap.new_socket()
	socket:set_timeout(15000)
	
	socket:connect(host.ip, port.number)
	
	-- read the reponse from the server
	local status
	local response
	
	status, response = socket:receive_bytes(1)

	if (not status) or (response == "TIMEOUT") then
		socket:close()
		return nil
	end
	
	-- check to see if the connection resembles an authorized MySQL server connection
	local major, minor, point = string.match(response, "%z\010(%d+)%.(%d+)%.([%w%-]*)")
	
	if major ~= nil and minor ~= nil and point ~= nil then
		-- send the login packet
		socket:send(strbuf.dump(payload))
		
		-- read in any response we might get
		status, response = socket:receive_bytes(1)

		if status and (response ~= "TIMEOUT") then
			if string.match(response, "\007%z%z\002%z%z%z\002") then
				return socket
			end
		end
	end
	
	socket:close()
	return nil
end

-- attempts to login to service with the given username and password combination
-- if login is successful, returns a socket with an open connection to the server
-- if login is unsuccessful, returns nil
function try_login(host, port, username, password)

	if not nmap.have_ssl() then
		stdnse.print_debug("OpenSSL support required to attempt password logins!")
		return nil
	end
	
	if password == nil then
		stdnse.print_debug("No password detected! Use try_login_np instead")
		return nil
	end
	
	-- create the socket used for our connection, set a reasonable timeout
	local socket = nmap.new_socket()
	socket:set_timeout(15000)
	
	socket:connect(host.ip, port.number)
	
	-- read the reponse from the server
	local status
	local response
	
	status, response = socket:receive_bytes(1)

	if (not status) or (response == "TIMEOUT") then
		socket:close()
		return nil
	end

	-- check to see if the connection resembles an authorized MySQL server connection
	local major, minor, point = string.match(response, "%z\010(%d+)%.(%d+)%.([%w%-]*)")
	
	if major ~= nil and minor ~= nil and point ~= nil then
		local a, b, c = string.byte(response, 1, 3)
		local plen = a + 256*(b + 256*(c))
		if string.len(response) ~= (plen + 4) then
			stdnse.print_debug(" !! Received packet of unknown format!\n")
			stdnse.print_debug(" Packet length: " .. string.format("%d", string.len(response)) .. " Expected length: " .. plen)
			socket:close()
			return nil
		end
		
		local salt1, salt2
		-- check if the server uses the new style of password hashing
		if (major == "5" or major == "6") or
			(major == "4" and minor == "1" ) then
			salt1 = string.sub(response, plen - 35, plen - 28)
			salt2 = string.sub(response, plen - 8, plen + 3)
				
			-- calculate the password hash to send back to MySQL server
			-- password algorithm taken from http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html
			local salt = salt1 .. salt2
			local hash1 = openssl.digest_sha1(password, true)
			local hash2 = openssl.digest_sha1(hash1, true)
			local hash3 = openssl.digest_sha1(salt .. hash2, true)
				
			local final = ""
			for i = 1, 20, 1 do
				final = final .. string.char(bit.bxor(string.byte(hash3,i), string.byte(hash1,i)))
			end
				
			local payload = strbuf.new()
			local pay_len = string.len(username) + 54
			payload = payload .. string.char(pay_len) .. "\000\000\001\133\166\003\000"
			payload = payload .. "\000\000\000\001\008\000\000\000"
			payload = payload .. "\000\000\000\000\000\000\000\000"
			payload = payload .. "\000\000\000\000\000\000\000\000"
			payload = payload .. "\000\000\000\000" .. username
			payload = payload .. "\000\020" .. final
				
			socket:send(strbuf.dump(payload))
				
			-- read in any response we might get
			status, response = socket:receive_bytes(1)

			if status and (response ~= "TIMEOUT") then
				if string.match(response, "\007%z%z\002%z%z%z\002") then
					return socket
				end
			else
				socket:close()
				return nil
			end
		end
	end
	socket:close()
	return nil
end

-- retrieves the status of certain server variables
-- requires a socket that has successfully authenticated to the server
function show_status(socket)
	
	local payload = strbuf.new()
	local value = strbuf.new()
	
	-- Get the uptime of the MySQL server
	payload = payload .. "\026\000\000\000\003"
	payload = payload .. "SHOW STATUS LIKE 'Uptime'"
	
	socket:send(strbuf.dump(payload))
	
	-- read the reponse from the server
	local status, response = socket:receive_bytes(1)
	if (not status) or response == "TIMEOUT" or response == nil then
		return nil
	end

	local uptime = parse_status(response, "Uptime")
	if uptime ~= nil then
		local secs = tonumber(uptime)
		-- if uptime is less than 2 minutes, just report seconds
		if secs < 120 then
			value = value .. "   Server uptime: " .. secs .. " seconds.\n"
		-- if uptime is less than two hours, just report minutes and seconds
		elseif secs < 7200 then
			local minutes = math.floor(secs / 60)
			local seconds = secs % 60
			value = value .. "   Server uptime: " .. minutes .. " minutes, " .. seconds .. " seconds.\n"
		-- if uptime is less than two days, then report hours, minutes and seconds
		elseif secs < 172800 then
			local hours = math.floor(secs / 3600)
			local minutes = math.floor((secs % 3600) / 60)
			local seconds = secs % 60
			value = value .. "   Server uptime: " .. hours .. " hours, " .. minutes .. " minutes, " .. seconds .. " seconds.\n"
		--if uptime is less than two weeks, then report days, hours, minutes
		elseif secs < 1209600 then
			local days = math.floor(secs / 86400)
			local hours = math.floor((secs % 86400) / 3600)
			local minutes = (secs % 3600) / 60
			value = value .. "   Server uptime: " .. days .. " days, " .. hours .. " hours, " .. minutes .. " minutes.\n"
		end
	end
	
	strbuf.clear(payload)
	-- Get the Questions variable from the MySQL server, which indicates how many queries the server has handled
	payload = payload .. "\048\000\000\000\003"
	payload = payload .. "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Questions'"
	
	socket:send(strbuf.dump(payload))
	status, response = socket:receive_bytes(1)
	if (not status) or response == "TIMEOUT" or response == nil then
		return nil
	end

	local questions = parse_status(response, "Questions")
	
	if questions ~= nil then
		value = value .. "   Statements processed: " .. questions
		if uptime ~= nil then
			value = value .. string.format(" (%3.2f per second)\n", tonumber(questions) / tonumber(uptime))
		else
			value = value .. "\n"
		end
	end
	
	strbuf.clear(payload)
	-- Get the total number of connection attempts to the MySQL server
	payload = payload .. "\031\000\000\000\003"
	payload = payload .. "SHOW STATUS LIKE 'Connections'"
	
	socket:send(strbuf.dump(payload))
	status, response = socket:receive_bytes(1)
	if (not status) or response == "TIMEOUT" or response == nil then
		return nil
	end

	local connections = parse_status(response, "Connections")
	
	strbuf.clear(payload)
	-- Get the open_files and open_tables variables from the MySQL server
	payload = payload .. "\036\000\000\000\003"
	payload = payload .. "SHOW STATUS LIKE 'Aborted_connects'"
	
	socket:send(strbuf.dump(payload))
	status, response = socket:receive_bytes(1)
	if (not status) or response == "TIMEOUT" or response == nil then
		return nil
	end
	
	local aborts = parse_status(response, "Aborted_connects")
	
	if connections ~= nil and aborts ~= nil then
		local successful = tonumber(connections) - tonumber(aborts)
		value = value .. "   Connections: " .. successful .. " successful, " .. aborts .. " unsuccessful.\n"
	end
	
	
	strbuf.clear(payload)
	-- Get the open_files and open_tables variables from the MySQL server
	payload = payload .. "\045\000\000\000\003"
	payload = payload .. "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Open_%'"
	
	socket:send(strbuf.dump(payload))
	status, response = socket:receive_bytes(1)
	if (not status) or response == "TIMEOUT" or response == nil then
		return nil
	end

	local open_files = parse_status(response, "Open_files")
	local open_tables = parse_status(response, "Open_tables")
	
	if open_files ~= nil then
		value = value .. "   Open files: " .. open_files
	end
	if open_tables ~= nil then
		value = value .. "           Open tables: " .. open_tables .. "\n"
	end
	
	strbuf.clear(payload)
	-- Get the Bytes_received and Bytes_send variables from the MySQL server
	-- Use syntax to obtain global variables
	payload = payload .. "\046\000\000\000\003"
	payload = payload .. "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Bytes_%'"
	
	socket:send(strbuf.dump(payload))
	status, response = socket:receive_bytes(1)
	if (not status) or response == "TIMEOUT" or response == nil then
		return nil
	end

	local in_bytes = parse_status(response, "Bytes_received")
	local out_bytes = parse_status(response, "Bytes_sent")
	
	if in_bytes ~= nil then
		value = value .. "   Bytes received: " .. in_bytes
	end
	if out_bytes ~= nil then
		value = value .. "    Bytes send: " .. out_bytes .. "\n"
	end
	
	return value
end

-- parses a server status response for a specific variable
function parse_status(response, pattern)
	
	local more = true
	local eof_count = 0
	local plen, pnum, fle
	local a, b, c
	local pcount = 0
	local db_list = {}
	while more == true do
		pcount = pcount + 1
		a, b, c = string.byte(response, 1, 3)
		plen = a + 256*(b + 256*c)
		stdnse.print_debug("Packet of length " .. plen .. " detected")
		pnum = string.byte(response, 4)
		stdnse.print_debug("Packet number " .. pnum .. " detected")
		if pnum ~= pcount then
			stdnse.print_debug("Packet number (" .. pnum .. ") did not match packet count (" .. pcount .. ")")
			return nil
		end
		
		-- retrieve field length encoding
		fle = string.byte(response, 5)
		if fle == 0 then
			stdnse.print_debug("Simple response detected where tabular response was expected!")
			return nil
		elseif fle == 1 and pnum == 1 then
			stdnse.print_debug("Initial response packet detected")
		elseif fle == 3 and pnum == 2 then
			stdnse.print_debug("Field description packet detected")
		elseif fle == 254 then
			if eof_count > 0 then
				stdnse.print_debug("Tabular response end detected")
				more = false
			else
				stdnse.print_debug("Tabular response start detected")
				eof_count = eof_count + 1
			end
		else
			-- extract the data from this MySQL packet
			local pdata = string.sub(response, 5, 4 + plen)
			-- check to see if the variable we want is in this packet
			if string.match(pdata, pattern) ~= nil then
				-- if we got a successful match, then extract the value for the status variable
				-- start by getting the Length Coded Binary that tells us the length of the string
				local var_name_len = string.byte(pdata, 1)
				-- if the length is between 1 and 250, no further processing is needed
				-- TODO - process length 252, 253, and 254, which indicate longer strings
				if var_name_len > 0 and var_name_len < 251 then
					-- grab the string whose length we have calculated
					local var_name = string.sub(pdata, 2, var_name_len + 1)
					-- verify that it matches the pattern we are looking for
					if var_name == pattern and string.len(pdata) > (var_name_len + 1) then
						-- throw away the portion of the packet we have already processed, and move on
						pdata = string.sub(pdata, var_name_len + 2)
						-- get the length of the variable value
						local var_value_len = string.byte(pdata, 1)
						-- extract the variable value from the remainder of the packet
						if var_value_len > 0 and var_value_len < 251 then
							local var_value = string.sub(pdata, 2, var_value_len + 1)
							return var_value
						end
					end
				end
			end	 
		end
		
		if string.len(response) > (plen + 5) then
			response = string.sub(response, plen + 5)
		else
			more = false
		end
	end	
	
	return nil
end

-- retrieves a list of databases on the server
-- requires a socket that has successfully authenticated to the server
function show_db(socket)

	local payload = strbuf.new()
	
	payload = payload .. "\015\000\000\000\003"
	payload = payload .. "show databases"
	
	socket:send(strbuf.dump(payload))
	
	-- read the reponse from the server
	local status, response = socket:receive_bytes(1)
	if (not status) or response == "TIMEOUT" or response == nil then
		return nil
	end

	-- parse the MySQL response
	local more = true
	local eof_count = 0
	local plen, pnum, fle
	local a, b, c
	local pcount = 0
	local db_list = {}
	while more == true do
		pcount = pcount + 1
		a, b, c = string.byte(response, 1, 3)
		plen = a + 256*(b + 256*c)
		stdnse.print_debug("Packet of length " .. plen .. " detected")
		pnum = string.byte(response, 4)
		stdnse.print_debug("Packet number " .. pnum .. " detected")
		if pnum ~= pcount then
			stdnse.print_debug("Packet number (" .. pnum .. ") did not match packet count (" .. pcount .. ")")
			return nil
		end
		
		-- retrieve field length encoding
		fle = string.byte(response, 5)
		if fle == 0 then
			stdnse.print_debug("Simple response detected where tabular response was expected!")
			return nil
		elseif fle == 1 and pnum == 1 then
			stdnse.print_debug("Initial response packet detected")
		elseif fle == 3 and pnum == 2 then
			stdnse.print_debug("Field description packet detected")
		elseif fle == 254 then
			if eof_count > 0 then
				stdnse.print_debug("Tabular response end detected")
				more = false
			else
				stdnse.print_debug("Tabular response start detected")
				eof_count = eof_count + 1
			end
		elseif fle == (plen - 1) then
			local db_name = string.sub(response, 6, 5 + fle)
			db_list = listop.append(db_list, {db_name})
		else
			if pnum > 2 then
				stdnse.print_debug("Unknown MySQL packet detected")
				stdnse.print_debug("FLE: " .. fle .. "   Packet #: " .. pnum)
			end
		end

		if string.len(response) > (plen + 5) then
			response = string.sub(response, plen + 5)
		else
			more = false
		end
	end

	if listop.is_empty(db_list) then
		return nil
	else
		return db_list
	end
end	

action = function(host, port)
	-- here is the place to add additional. usernames and passwords for more extensive testing
	local users = {"", "admin", "mysql", "root"}
	local passwords = {"admin", "mysql", "password", "Password", "root", "toor"}
	local sock
	local output = strbuf.new()
	for _, user in ipairs(users) do
		-- try to login with each username, without a password
		sock = try_login_np(host, port, user)
		if sock ~= nil then
			if string.len(user) == 0 then
				output = output .. " Able to login to MySQL server without a username and password.\n"
			else
				output = output .. " Able to login to MySQL server with username " .. user .. " and no password.\n"
			end
			
			-- if we got a successful login, then try to the databases present on the server
			local db_names = show_db(sock)
			if db_names ~= nil then
				output = output .. "  The following database(s) were detected:\n"
				local line = ""
				-- try to squeeze multiple databases onto a single line, but avoid making really long lines
				for _,db_name in ipairs(db_names) do
					if string.len(line) > 40 then
						output = output .. line .. "\n"
					elseif string.len(line) == 0 then
						line = line .. "   " .. db_name
					else
						line = line .. ", " .. db_name 
					end
				end
				output = output .. line .. "\n"
			else
				stdnse.print_debug("No databases detected")
			end
			
			-- before we close the connection, try to obtain some additional information from the server			
			local status = show_status(sock)
			output = output .. status
			-- Send MySQL quit request
			sock:send("\001\000\000\000\001")
			sock:close()
		end
		
		for _, pass in ipairs(passwords) do
			-- try each username with each possible password, but don't try to login with no username
			if string.len(user) > 0 then
				sock = try_login(host, port, user, pass)
				if sock ~= nil then
					output = output .. " Able to login to MySQL server with username " .. user .. " and password \"" .. pass .. "\".\n"
					local db_names = show_db(sock)
					if db_names ~= nil then
						output = output .. "  The following database(s) were detected:\n"
						local line = ""
						for _,db_name in ipairs(db_names) do
							if string.len(line) > 40 then
								output = output .. line .. "\n"
							elseif string.len(line) == 0 then
								line = line .. "   " .. db_name
							else
								line = line .. ", " .. db_name 
							end
						end
						output = output .. line .. "\n"
					else
						stdnse.print_debug("No databases detected")
					end
					local status = show_status(sock)
					if status ~= nil then
						output = output .. "  Server status variables:\n"
						output = output .. status
					end
					-- Send MySQL quit request
					sock:send("\001\000\000\000\001")
					sock:close()
				end
			end
		end
	end
	
	if string.len(strbuf.dump(output)) > 0 then
		return strbuf.dump(output)
	else
		return nil
	end
end