mysql_protocol.erl 51 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163
  1. %% MySQL/OTP – MySQL client library for Erlang/OTP
  2. %% Copyright (C) 2014 Viktor Söderqvist
  3. %%
  4. %% This file is part of MySQL/OTP.
  5. %%
  6. %% MySQL/OTP is free software: you can redistribute it and/or modify it under
  7. %% the terms of the GNU Lesser General Public License as published by the Free
  8. %% Software Foundation, either version 3 of the License, or (at your option)
  9. %% any later version.
  10. %%
  11. %% This program is distributed in the hope that it will be useful, but WITHOUT
  12. %% ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
  13. %% FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
  14. %% more details.
  15. %%
  16. %% You should have received a copy of the GNU Lesser General Public License
  17. %% along with this program. If not, see <https://www.gnu.org/licenses/>.
  18. %% @doc This module implements parts of the MySQL client/server protocol.
  19. %%
  20. %% The protocol is described in the document "MySQL Internals" which can be
  21. %% found under "MySQL Documentation: Expert Guides" on http://dev.mysql.com/.
  22. %%
  23. %% TCP communication is not handled in this module. Most of the public functions
  24. %% take funs for data communitaction as parameters.
  25. %% @private
  26. -module(mysql_protocol).
  27. -export([handshake/5, quit/2, ping/2,
  28. query/4, fetch_query_response/3,
  29. prepare/3, unprepare/3, execute/5, fetch_execute_response/3]).
  30. %% How much data do we want per packet?
  31. -define(MAX_BYTES_PER_PACKET, 16#1000000).
  32. -include("records.hrl").
  33. -include("protocol.hrl").
  34. -include("server_status.hrl").
  35. %% Macros for pattern matching on packets.
  36. -define(ok_pattern, <<?OK, _/binary>>).
  37. -define(error_pattern, <<?ERROR, _/binary>>).
  38. -define(eof_pattern, <<?EOF, _:4/binary>>).
  39. %% @doc Performs a handshake using the supplied functions for communication.
  40. %% Returns an ok or an error record. Raises errors when various unimplemented
  41. %% features are requested.
  42. -spec handshake(iodata(), iodata(), iodata() | undefined, atom(), term()) ->
  43. #handshake{} | #error{}.
  44. handshake(Username, Password, Database, TcpModule, Socket) ->
  45. SeqNum0 = 0,
  46. {ok, HandshakePacket, SeqNum1} = recv_packet(TcpModule, Socket, SeqNum0),
  47. Handshake = parse_handshake(HandshakePacket),
  48. Response = build_handshake_response(Handshake, Username, Password,
  49. Database),
  50. {ok, SeqNum2} = send_packet(TcpModule, Socket, Response, SeqNum1),
  51. {ok, ConfirmPacket, _SeqNum3} = recv_packet(TcpModule, Socket, SeqNum2),
  52. case parse_handshake_confirm(ConfirmPacket) of
  53. #ok{status = OkStatus} ->
  54. OkStatus = Handshake#handshake.status,
  55. Handshake;
  56. Error ->
  57. Error
  58. end.
  59. -spec quit(atom(), term()) -> ok.
  60. quit(TcpModule, Socket) ->
  61. {ok, SeqNum1} = send_packet(TcpModule, Socket, <<?COM_QUIT>>, 0),
  62. case recv_packet(TcpModule, Socket, SeqNum1) of
  63. {error, closed} -> ok; %% MySQL 5.5.40 and more
  64. {ok, ?ok_pattern, _SeqNum2} -> ok %% Some older MySQL versions?
  65. end.
  66. -spec ping(atom(), term()) -> #ok{}.
  67. ping(TcpModule, Socket) ->
  68. {ok, SeqNum1} = send_packet(TcpModule, Socket, <<?COM_PING>>, 0),
  69. {ok, OkPacket, _SeqNum2} = recv_packet(TcpModule, Socket, SeqNum1),
  70. parse_ok_packet(OkPacket).
  71. -spec query(Query :: iodata(), atom(), term(), timeout()) ->
  72. {ok, [#ok{} | #resultset{} | #error{}]} | {error, timeout}.
  73. query(Query, TcpModule, Socket, Timeout) ->
  74. Req = <<?COM_QUERY, (iolist_to_binary(Query))/binary>>,
  75. SeqNum0 = 0,
  76. {ok, _SeqNum1} = send_packet(TcpModule, Socket, Req, SeqNum0),
  77. fetch_query_response(TcpModule, Socket, Timeout).
  78. %% @doc This is used by query/4. If query/4 returns {error, timeout}, this
  79. %% function can be called to retry to fetch the results of the query.
  80. fetch_query_response(TcpModule, Socket, Timeout) ->
  81. fetch_response(TcpModule, Socket, Timeout, text, []).
  82. %% @doc Prepares a statement.
  83. -spec prepare(iodata(), atom(), term()) -> #error{} | #prepared{}.
  84. prepare(Query, TcpModule, Socket) ->
  85. Req = <<?COM_STMT_PREPARE, (iolist_to_binary(Query))/binary>>,
  86. {ok, SeqNum1} = send_packet(TcpModule, Socket, Req, 0),
  87. {ok, Resp, SeqNum2} = recv_packet(TcpModule, Socket, SeqNum1),
  88. case Resp of
  89. ?error_pattern ->
  90. parse_error_packet(Resp);
  91. <<?OK,
  92. StmtId:32/little,
  93. NumColumns:16/little,
  94. NumParams:16/little,
  95. 0, %% reserved_1 -- [00] filler
  96. WarningCount:16/little>> ->
  97. %% This was the first packet.
  98. %% Now: Parameter Definition Block. The parameter definitions don't
  99. %% contain any useful data at all. They are always TYPE_VAR_STRING
  100. %% with charset 'binary' so we have to select a type ourselves for
  101. %% the parameters we have in execute/4.
  102. {_ParamDefs, SeqNum3} =
  103. fetch_column_definitions_if_any(NumParams, TcpModule, Socket,
  104. SeqNum2),
  105. %% Column Definition Block. We get column definitions in execute
  106. %% too, so we don't need them here. We *could* store them to be able
  107. %% to provide the user with some info about a prepared statement.
  108. {_ColDefs, _SeqNum4} =
  109. fetch_column_definitions_if_any(NumColumns, TcpModule, Socket,
  110. SeqNum3),
  111. #prepared{statement_id = StmtId,
  112. orig_query = Query,
  113. param_count = NumParams,
  114. warning_count = WarningCount}
  115. end.
  116. %% @doc Deallocates a prepared statement.
  117. -spec unprepare(#prepared{}, atom(), term()) -> ok.
  118. unprepare(#prepared{statement_id = Id}, TcpModule, Socket) ->
  119. {ok, _SeqNum} = send_packet(TcpModule, Socket,
  120. <<?COM_STMT_CLOSE, Id:32/little>>, 0),
  121. ok.
  122. %% @doc Executes a prepared statement.
  123. -spec execute(#prepared{}, [term()], atom(), term(), timeout()) ->
  124. {ok, [#ok{} | #resultset{} | #error{}]} | {error, timeout}.
  125. execute(#prepared{statement_id = Id, param_count = ParamCount}, ParamValues,
  126. TcpModule, Socket, Timeout) when ParamCount == length(ParamValues) ->
  127. %% Flags Constant Name
  128. %% 0x00 CURSOR_TYPE_NO_CURSOR
  129. %% 0x01 CURSOR_TYPE_READ_ONLY
  130. %% 0x02 CURSOR_TYPE_FOR_UPDATE
  131. %% 0x04 CURSOR_TYPE_SCROLLABLE
  132. Flags = 0,
  133. Req0 = <<?COM_STMT_EXECUTE, Id:32/little, Flags, 1:32/little>>,
  134. Req = case ParamCount of
  135. 0 ->
  136. Req0;
  137. _ ->
  138. %% We can't use the parameter types returned by the prepare call.
  139. %% They are all reported as ?TYPE_VAR_STRING with character
  140. %% set 'binary'.
  141. NullBitMap = build_null_bitmap(ParamValues),
  142. %% What does it mean to *not* bind new params? To use the same
  143. %% params as last time? Right now we always bind params each time.
  144. NewParamsBoundFlag = 1,
  145. Req1 = <<Req0/binary, NullBitMap/binary, NewParamsBoundFlag>>,
  146. %% For each value, first append type and signedness (16#80 signed or
  147. %% 00 unsigned) for all values and then the binary encoded values.
  148. EncodedParams = lists:map(fun encode_param/1, ParamValues),
  149. {TypesAndSigns, EncValues} = lists:unzip(EncodedParams),
  150. iolist_to_binary([Req1, TypesAndSigns, EncValues])
  151. end,
  152. {ok, _SeqNum1} = send_packet(TcpModule, Socket, Req, 0),
  153. fetch_execute_response(TcpModule, Socket, Timeout).
  154. %% @doc This is used by execute/5. If execute/5 returns {error, timeout}, this
  155. %% function can be called to retry to fetch the results of the query.
  156. fetch_execute_response(TcpModule, Socket, Timeout) ->
  157. fetch_response(TcpModule, Socket, Timeout, binary, []).
  158. %% --- internal ---
  159. %% @doc Parses a handshake. This is the first thing that comes from the server
  160. %% when connecting. If an unsupported version or variant of the protocol is used
  161. %% an error is raised.
  162. -spec parse_handshake(binary()) -> #handshake{}.
  163. parse_handshake(<<10, Rest/binary>>) ->
  164. %% Protocol version 10.
  165. {ServerVersion, Rest1} = nulterm_str(Rest),
  166. <<ConnectionId:32/little,
  167. AuthPluginDataPart1:8/binary-unit:8,
  168. 0, %% "filler" -- everything below is optional
  169. CapabilitiesLower:16/little,
  170. CharacterSet:8,
  171. StatusFlags:16/little,
  172. CapabilitiesUpper:16/little,
  173. AuthPluginDataLength:8, %% if cabab & CLIENT_PLUGIN_AUTH, otherwise 0
  174. _Reserved:10/binary-unit:8, %% 10 unused (reserved) bytes
  175. Rest3/binary>> = Rest1,
  176. Capabilities = CapabilitiesLower + 16#10000 * CapabilitiesUpper,
  177. Len = case AuthPluginDataLength of
  178. 0 -> 13; %% Server has not CLIENT_PLUGIN_AUTH
  179. K -> K - 8 %% Part 2 length = Total length minus the 8 bytes in part 1.
  180. end,
  181. <<AuthPluginDataPart2:Len/binary-unit:8, AuthPluginName/binary>> = Rest3,
  182. AuthPluginData = <<AuthPluginDataPart1/binary, AuthPluginDataPart2/binary>>,
  183. %% "Due to Bug#59453 the auth-plugin-name is missing the terminating
  184. %% NUL-char in versions prior to 5.5.10 and 5.6.2."
  185. %% Strip the final NUL byte if any.
  186. %% This may also be <<>> in older versions.
  187. L = byte_size(AuthPluginName) - 1,
  188. AuthPluginName1 = case AuthPluginName of
  189. <<AuthPluginNameTrimmed:L/binary, 0>> -> AuthPluginNameTrimmed;
  190. _ -> AuthPluginName
  191. end,
  192. #handshake{server_version = server_version_to_list(ServerVersion),
  193. connection_id = ConnectionId,
  194. capabilities = Capabilities,
  195. character_set = CharacterSet,
  196. status = StatusFlags,
  197. auth_plugin_data = AuthPluginData,
  198. auth_plugin_name = AuthPluginName1};
  199. parse_handshake(<<Protocol:8, _/binary>>) when Protocol /= 10 ->
  200. error(unknown_protocol).
  201. %% @doc Converts a version on the form `<<"5.6.21">' to a list `[5, 6, 21]'.
  202. -spec server_version_to_list(binary()) -> [integer()].
  203. server_version_to_list(ServerVersion) ->
  204. %% This must work with e.g. "5.5.40-0ubuntu0.12.04.1-log" and "5.5.33a".
  205. {match, Parts} = re:run(ServerVersion, <<"^(\\d+)\\.(\\d+)\\.(\\d+)">>,
  206. [{capture, all_but_first, binary}]),
  207. lists:map(fun binary_to_integer/1, Parts).
  208. %% @doc The response sent by the client to the server after receiving the
  209. %% initial handshake from the server
  210. -spec build_handshake_response(#handshake{}, iodata(), iodata(),
  211. iodata() | undefined) -> binary().
  212. build_handshake_response(Handshake, Username, Password, Database) ->
  213. %% We require these capabilities. Make sure the server handles them.
  214. CapabilityFlags0 = ?CLIENT_PROTOCOL_41 bor
  215. ?CLIENT_TRANSACTIONS bor
  216. ?CLIENT_SECURE_CONNECTION,
  217. CapabilityFlags = case Database of
  218. undefined -> CapabilityFlags0;
  219. _ -> CapabilityFlags0 bor ?CLIENT_CONNECT_WITH_DB
  220. end,
  221. Handshake#handshake.capabilities band CapabilityFlags == CapabilityFlags
  222. orelse error(old_server_version),
  223. %% Add some extra capability flags only for signalling to the server what
  224. %% the client wants to do. The server doesn't say it handles them although
  225. %% it does. (http://bugs.mysql.com/bug.php?id=42268)
  226. ClientCapabilityFlags = CapabilityFlags bor
  227. ?CLIENT_MULTI_STATEMENTS bor
  228. ?CLIENT_MULTI_RESULTS bor
  229. ?CLIENT_PS_MULTI_RESULTS,
  230. Hash = case Handshake#handshake.auth_plugin_name of
  231. <<>> ->
  232. %% Server doesn't know auth plugins
  233. hash_password(Password, Handshake#handshake.auth_plugin_data);
  234. <<"mysql_native_password">> ->
  235. hash_password(Password, Handshake#handshake.auth_plugin_data);
  236. UnknownAuthMethod ->
  237. error({auth_method, UnknownAuthMethod})
  238. end,
  239. HashLength = size(Hash),
  240. CharacterSet = ?UTF8,
  241. UsernameUtf8 = unicode:characters_to_binary(Username),
  242. DbBin = case Database of
  243. undefined -> <<>>;
  244. _ -> <<(iolist_to_binary(Database))/binary, 0>>
  245. end,
  246. <<ClientCapabilityFlags:32/little,
  247. ?MAX_BYTES_PER_PACKET:32/little,
  248. CharacterSet:8,
  249. 0:23/unit:8, %% reserverd
  250. UsernameUtf8/binary,
  251. 0, %% NUL-terminator for the username
  252. HashLength,
  253. Hash/binary,
  254. DbBin/binary>>.
  255. %% @doc Handles the second packet from the server, when we have replied to the
  256. %% initial handshake. Returns an error if the server returns an error. Raises
  257. %% an error if unimplemented features are required.
  258. -spec parse_handshake_confirm(binary()) -> #ok{} | #error{}.
  259. parse_handshake_confirm(Packet) ->
  260. case Packet of
  261. ?ok_pattern ->
  262. %% Connection complete.
  263. parse_ok_packet(Packet);
  264. ?error_pattern ->
  265. %% Access denied, insufficient client capabilities, etc.
  266. parse_error_packet(Packet);
  267. <<?EOF>> ->
  268. %% "Old Authentication Method Switch Request Packet consisting of a
  269. %% single 0xfe byte. It is sent by server to request client to
  270. %% switch to Old Password Authentication if CLIENT_PLUGIN_AUTH
  271. %% capability is not supported (by either the client or the server)"
  272. error(old_auth);
  273. <<?EOF, _/binary>> ->
  274. %% "Authentication Method Switch Request Packet. If both server and
  275. %% client support CLIENT_PLUGIN_AUTH capability, server can send
  276. %% this packet to ask client to use another authentication method."
  277. error(auth_method_switch)
  278. end.
  279. %% -- both text and binary protocol --
  280. %% @doc Fetches one or more results and and parses the result set(s) using
  281. %% either the text format (for plain queries) or the binary format (for
  282. %% prepared statements).
  283. -spec fetch_response(atom(), term(), timeout(), text | binary, list()) ->
  284. {ok, [#ok{} | #resultset{} | #error{}]} | {error, timeout}.
  285. fetch_response(TcpModule, Socket, Timeout, Proto, Acc) ->
  286. case recv_packet(TcpModule, Socket, Timeout, any) of
  287. {ok, Packet, SeqNum2} ->
  288. Result = case Packet of
  289. ?ok_pattern ->
  290. parse_ok_packet(Packet);
  291. ?error_pattern ->
  292. parse_error_packet(Packet);
  293. ResultPacket ->
  294. %% The first packet in a resultset is only the column count.
  295. {ColCount, <<>>} = lenenc_int(ResultPacket),
  296. R0 = fetch_resultset(TcpModule, Socket, ColCount, SeqNum2),
  297. case R0 of
  298. #error{} = E ->
  299. %% TODO: Find a way to get here + testcase
  300. E;
  301. #resultset{} = R ->
  302. parse_resultset(R, ColCount, Proto)
  303. end
  304. end,
  305. Acc1 = [Result | Acc],
  306. case more_results_exists(Result) of
  307. true ->
  308. fetch_response(TcpModule, Socket, Timeout, Proto, Acc1);
  309. false ->
  310. {ok, lists:reverse(Acc1)}
  311. end;
  312. {error, timeout} ->
  313. {error, timeout}
  314. end.
  315. %% @doc Fetches packets for a result set. The column definitions are parsed but
  316. %% the rows are unparsed binary packages. This function is used for both the
  317. %% text protocol and the binary protocol. This affects the way the rows need to
  318. %% be parsed.
  319. -spec fetch_resultset(atom(), term(), integer(), integer()) ->
  320. #resultset{} | #error{}.
  321. fetch_resultset(TcpModule, Socket, FieldCount, SeqNum) ->
  322. {ok, ColDefs, SeqNum1} = fetch_column_definitions(TcpModule, Socket, SeqNum,
  323. FieldCount, []),
  324. {ok, DelimiterPacket, SeqNum2} = recv_packet(TcpModule, Socket, SeqNum1),
  325. #eof{status = S, warning_count = W} = parse_eof_packet(DelimiterPacket),
  326. case fetch_resultset_rows(TcpModule, Socket, SeqNum2, []) of
  327. {ok, Rows, _SeqNum3} ->
  328. ColDefs1 = lists:map(fun parse_column_definition/1, ColDefs),
  329. #resultset{cols = ColDefs1, rows = Rows,
  330. status = S, warning_count = W};
  331. #error{} = E ->
  332. E
  333. end.
  334. parse_resultset(#resultset{cols = ColDefs, rows = Rows} = R, ColumnCount, text) ->
  335. %% Parse the rows according to the 'text protocol' representation.
  336. Rows1 = [decode_text_row(ColumnCount, ColDefs, Row) || Row <- Rows],
  337. R#resultset{rows = Rows1};
  338. parse_resultset(#resultset{cols = ColDefs, rows = Rows} = R, ColumnCount, binary) ->
  339. %% Parse the rows according to the 'binary protocol' representation.
  340. Rows1 = [decode_binary_row(ColumnCount, ColDefs, Row) || Row <- Rows],
  341. R#resultset{rows = Rows1}.
  342. more_results_exists(#ok{status = S}) ->
  343. S band ?SERVER_MORE_RESULTS_EXISTS /= 0;
  344. more_results_exists(#error{}) ->
  345. false; %% No status bits for error
  346. more_results_exists(#resultset{status = S}) ->
  347. S band ?SERVER_MORE_RESULTS_EXISTS /= 0.
  348. %% @doc Receives NumLeft column definition packets. They are not parsed.
  349. %% @see parse_column_definition/1
  350. -spec fetch_column_definitions(atom(), term(), SeqNum :: integer(),
  351. NumLeft :: integer(), Acc :: [binary()]) ->
  352. {ok, ColDefPackets :: [binary()], NextSeqNum :: integer()}.
  353. fetch_column_definitions(TcpModule, Socket, SeqNum, NumLeft, Acc)
  354. when NumLeft > 0 ->
  355. {ok, Packet, SeqNum1} = recv_packet(TcpModule, Socket, SeqNum),
  356. fetch_column_definitions(TcpModule, Socket, SeqNum1, NumLeft - 1,
  357. [Packet | Acc]);
  358. fetch_column_definitions(_TcpModule, _Socket, SeqNum, 0, Acc) ->
  359. {ok, lists:reverse(Acc), SeqNum}.
  360. %% @doc Fetches rows in a result set. There is a packet per row. The row packets
  361. %% are not decoded. This function can be used for both the binary and the text
  362. %% protocol result sets.
  363. -spec fetch_resultset_rows(atom(), term(), SeqNum :: integer(), Acc) ->
  364. {ok, Rows, integer()} | #error{}
  365. when Acc :: [binary()],
  366. Rows :: [binary()].
  367. fetch_resultset_rows(TcpModule, Socket, SeqNum, Acc) ->
  368. {ok, Packet, SeqNum1} = recv_packet(TcpModule, Socket, SeqNum),
  369. case Packet of
  370. ?error_pattern ->
  371. parse_error_packet(Packet);
  372. ?eof_pattern ->
  373. {ok, lists:reverse(Acc), SeqNum1};
  374. Row ->
  375. fetch_resultset_rows(TcpModule, Socket, SeqNum1, [Row | Acc])
  376. end.
  377. %% Parses a packet containing a column definition (part of a result set)
  378. parse_column_definition(Data) ->
  379. {<<"def">>, Rest1} = lenenc_str(Data), %% catalog (always "def")
  380. {_Schema, Rest2} = lenenc_str(Rest1), %% schema-name
  381. {_Table, Rest3} = lenenc_str(Rest2), %% virtual table-name
  382. {_OrgTable, Rest4} = lenenc_str(Rest3), %% physical table-name
  383. {Name, Rest5} = lenenc_str(Rest4), %% virtual column name
  384. {_OrgName, Rest6} = lenenc_str(Rest5), %% physical column name
  385. {16#0c, Rest7} = lenenc_int(Rest6), %% length of the following fields
  386. %% (always 0x0c)
  387. <<Charset:16/little, %% column character set
  388. Length:32/little, %% maximum length of the field
  389. Type:8, %% type of the column as defined in Column Type
  390. Flags:16/little, %% flags
  391. Decimals:8, %% max shown decimal digits:
  392. 0, %% "filler" %% - 0x00 for integers and static strings
  393. 0, %% - 0x1f for dynamic strings, double, float
  394. Rest8/binary>> = Rest7, %% - 0x00 to 0x51 for decimals
  395. %% Here, if command was COM_FIELD_LIST {
  396. %% default values: lenenc_str
  397. %% }
  398. <<>> = Rest8,
  399. #col{name = Name, type = Type, charset = Charset, length = Length,
  400. decimals = Decimals, flags = Flags}.
  401. %% -- text protocol --
  402. -spec decode_text_row(NumColumns :: integer(),
  403. ColumnDefinitions :: [#col{}],
  404. Data :: binary()) -> [term()].
  405. decode_text_row(_NumColumns, ColumnDefs, Data) ->
  406. decode_text_row_acc(ColumnDefs, Data, []).
  407. %% parses Data using ColDefs and builds the values Acc.
  408. decode_text_row_acc([ColDef | ColDefs], Data, Acc) ->
  409. case Data of
  410. <<16#fb, Rest/binary>> ->
  411. %% NULL
  412. decode_text_row_acc(ColDefs, Rest, [null | Acc]);
  413. _ ->
  414. %% Every thing except NULL
  415. {Text, Rest} = lenenc_str(Data),
  416. Term = decode_text(ColDef, Text),
  417. decode_text_row_acc(ColDefs, Rest, [Term | Acc])
  418. end;
  419. decode_text_row_acc([], <<>>, Acc) ->
  420. lists:reverse(Acc).
  421. %% @doc When receiving data in the text protocol, we get everything as binaries
  422. %% (except NULL). This function is used to parse these string values.
  423. decode_text(#col{type = T}, Text)
  424. when T == ?TYPE_TINY; T == ?TYPE_SHORT; T == ?TYPE_LONG; T == ?TYPE_LONGLONG;
  425. T == ?TYPE_INT24; T == ?TYPE_YEAR ->
  426. binary_to_integer(Text);
  427. decode_text(#col{type = T}, Text)
  428. when T == ?TYPE_STRING; T == ?TYPE_VARCHAR; T == ?TYPE_VAR_STRING;
  429. T == ?TYPE_ENUM; T == ?TYPE_SET; T == ?TYPE_LONG_BLOB;
  430. T == ?TYPE_MEDIUM_BLOB; T == ?TYPE_BLOB; T == ?TYPE_TINY_BLOB;
  431. T == ?TYPE_GEOMETRY; T == ?TYPE_JSON ->
  432. %% As of MySQL 5.6.21 we receive SET and ENUM values as STRING, i.e. we
  433. %% cannot convert them to atom() or sets:set(), etc.
  434. Text;
  435. decode_text(#col{type = ?TYPE_BIT, length = Length}, Text) ->
  436. %% Convert to <<_:Length/bitstring>>
  437. decode_bitstring(Text, Length);
  438. decode_text(#col{type = T, decimals = S, length = L}, Text)
  439. when T == ?TYPE_DECIMAL; T == ?TYPE_NEWDECIMAL ->
  440. %% Length is the max number of symbols incl. dot and minus sign, e.g. the
  441. %% number of digits plus 2.
  442. decode_decimal(Text, L - 2, S);
  443. decode_text(#col{type = ?TYPE_DATE},
  444. <<Y:4/binary, "-", M:2/binary, "-", D:2/binary>>) ->
  445. {binary_to_integer(Y), binary_to_integer(M), binary_to_integer(D)};
  446. decode_text(#col{type = ?TYPE_TIME}, Text) ->
  447. {match, [Sign, Hbin, Mbin, Sbin, Frac]} =
  448. re:run(Text,
  449. <<"^(-?)(\\d+):(\\d+):(\\d+)(\\.?\\d*)$">>,
  450. [{capture, all_but_first, binary}]),
  451. H = binary_to_integer(Hbin),
  452. M = binary_to_integer(Mbin),
  453. S = binary_to_integer(Sbin),
  454. IsNeg = Sign == <<"-">>,
  455. Fraction = case Frac of
  456. <<>> -> 0;
  457. _ when not IsNeg -> binary_to_float(<<"0", Frac/binary>>);
  458. _ when IsNeg -> 1 - binary_to_float(<<"0", Frac/binary>>)
  459. end,
  460. Sec1 = H * 3600 + M * 60 + S,
  461. Sec2 = if IsNeg -> -Sec1; true -> Sec1 end,
  462. Sec3 = if IsNeg and (Fraction /= 0) -> Sec2 - 1;
  463. true -> Sec2
  464. end,
  465. {Days, {Hours, Minutes, Seconds}} = calendar:seconds_to_daystime(Sec3),
  466. {Days, {Hours, Minutes, Seconds + Fraction}};
  467. decode_text(#col{type = T},
  468. <<Y:4/binary, "-", M:2/binary, "-", D:2/binary, " ",
  469. H:2/binary, ":", Mi:2/binary, ":", S:2/binary>>)
  470. when T == ?TYPE_TIMESTAMP; T == ?TYPE_DATETIME ->
  471. %% Without fractions.
  472. {{binary_to_integer(Y), binary_to_integer(M), binary_to_integer(D)},
  473. {binary_to_integer(H), binary_to_integer(Mi), binary_to_integer(S)}};
  474. decode_text(#col{type = T},
  475. <<Y:4/binary, "-", M:2/binary, "-", D:2/binary, " ",
  476. H:2/binary, ":", Mi:2/binary, ":", FloatS/binary>>)
  477. when T == ?TYPE_TIMESTAMP; T == ?TYPE_DATETIME ->
  478. %% With fractions.
  479. {{binary_to_integer(Y), binary_to_integer(M), binary_to_integer(D)},
  480. {binary_to_integer(H), binary_to_integer(Mi), binary_to_float(FloatS)}};
  481. decode_text(#col{type = T}, Text) when T == ?TYPE_FLOAT;
  482. T == ?TYPE_DOUBLE ->
  483. try binary_to_float(Text)
  484. catch error:badarg ->
  485. try binary_to_integer(Text) of
  486. Int -> float(Int)
  487. catch error:badarg ->
  488. %% It is something like "4e75" that must be turned into "4.0e75"
  489. binary_to_float(binary:replace(Text, <<"e">>, <<".0e">>))
  490. end
  491. end.
  492. %% -- binary protocol --
  493. %% @doc If NumColumns is non-zero, fetches this number of column definitions
  494. %% and an EOF packet. Used by prepare/3.
  495. fetch_column_definitions_if_any(0, _TcpModule, _Socket, SeqNum) ->
  496. {[], SeqNum};
  497. fetch_column_definitions_if_any(N, TcpModule, Socket, SeqNum) ->
  498. {ok, Defs, SeqNum1} = fetch_column_definitions(TcpModule, Socket, SeqNum,
  499. N, []),
  500. {ok, ?eof_pattern, SeqNum2} = recv_packet(TcpModule, Socket, SeqNum1),
  501. {Defs, SeqNum2}.
  502. %% @doc Decodes a packet representing a row in a binary result set.
  503. %% It consists of a 0 byte, then a null bitmap, then the values.
  504. %% Returns a list of length NumColumns with terms of appropriate types for each
  505. %% MySQL type in ColumnTypes.
  506. -spec decode_binary_row(NumColumns :: integer(),
  507. ColumnDefs :: [#col{}],
  508. Data :: binary()) -> [term()].
  509. decode_binary_row(NumColumns, ColumnDefs, <<0, Data/binary>>) ->
  510. {NullBitMap, Rest} = null_bitmap_decode(NumColumns, Data, 2),
  511. decode_binary_row_acc(ColumnDefs, NullBitMap, Rest, []).
  512. %% @doc Accumulating helper for decode_binary_row/3.
  513. decode_binary_row_acc([_|ColDefs], <<1:1, NullBitMap/bitstring>>, Data, Acc) ->
  514. %% NULL
  515. decode_binary_row_acc(ColDefs, NullBitMap, Data, [null | Acc]);
  516. decode_binary_row_acc([ColDef | ColDefs], <<0:1, NullBitMap/bitstring>>, Data,
  517. Acc) ->
  518. %% Not NULL
  519. {Term, Rest} = decode_binary(ColDef, Data),
  520. decode_binary_row_acc(ColDefs, NullBitMap, Rest, [Term | Acc]);
  521. decode_binary_row_acc([], _, <<>>, Acc) ->
  522. lists:reverse(Acc).
  523. %% @doc Decodes a null bitmap as stored by MySQL and returns it in a strait
  524. %% bitstring counting bits from left to right in a tuple with remaining data.
  525. %%
  526. %% In the MySQL null bitmap the bits are stored counting bytes from the left and
  527. %% bits within each byte from the right. (Sort of little endian.)
  528. -spec null_bitmap_decode(NumColumns :: integer(), Data :: binary(),
  529. BitOffset :: integer()) ->
  530. {NullBitstring :: bitstring(), Rest :: binary()}.
  531. null_bitmap_decode(NumColumns, Data, BitOffset) ->
  532. %% Binary shift right by 3 is equivallent to integer division by 8.
  533. BitMapLength = (NumColumns + BitOffset + 7) bsr 3,
  534. <<NullBitstring0:BitMapLength/binary, Rest/binary>> = Data,
  535. <<_:BitOffset, NullBitstring:NumColumns/bitstring, _/bitstring>> =
  536. << <<(reverse_byte(B))/binary>> || <<B:1/binary>> <= NullBitstring0 >>,
  537. {NullBitstring, Rest}.
  538. %% @doc The reverse of null_bitmap_decode/3. The number of columns is taken to
  539. %% be the number of bits in NullBitstring. Returns the MySQL null bitmap as a
  540. %% binary (i.e. full bytes). BitOffset is the number of unused bits that should
  541. %% be inserted before the other bits.
  542. -spec null_bitmap_encode(bitstring(), integer()) -> binary().
  543. null_bitmap_encode(NullBitstring, BitOffset) ->
  544. PayloadLength = bit_size(NullBitstring) + BitOffset,
  545. %% Round up to a multiple of 8.
  546. BitMapLength = (PayloadLength + 7) band bnot 7,
  547. PadBitsLength = BitMapLength - PayloadLength,
  548. PaddedBitstring = <<0:BitOffset, NullBitstring/bitstring, 0:PadBitsLength>>,
  549. << <<(reverse_byte(B))/binary>> || <<B:1/binary>> <= PaddedBitstring >>.
  550. %% Reverses the bits in a byte.
  551. reverse_byte(<<A:1, B:1, C:1, D:1, E:1, F:1, G:1, H:1>>) ->
  552. <<H:1, G:1, F:1, E:1, D:1, C:1, B:1, A:1>>.
  553. %% @doc Used for executing prepared statements. The bit offset whould be 0 in
  554. %% this case.
  555. -spec build_null_bitmap([any()]) -> binary().
  556. build_null_bitmap(Values) ->
  557. Bits = << <<(case V of null -> 1; _ -> 0 end):1>> || V <- Values >>,
  558. null_bitmap_encode(Bits, 0).
  559. %% Decodes a value as received in the 'binary protocol' result set.
  560. %%
  561. %% The types are type constants for the binary protocol, such as
  562. %% ProtocolBinary::MYSQL_TYPE_STRING. In the guide "MySQL Internals" these are
  563. %% not listed, but we assume that are the same as for the text protocol.
  564. -spec decode_binary(ColDef :: #col{}, Data :: binary()) ->
  565. {Term :: term(), Rest :: binary()}.
  566. decode_binary(#col{type = T}, Data)
  567. when T == ?TYPE_STRING; T == ?TYPE_VARCHAR; T == ?TYPE_VAR_STRING;
  568. T == ?TYPE_ENUM; T == ?TYPE_SET; T == ?TYPE_LONG_BLOB;
  569. T == ?TYPE_MEDIUM_BLOB; T == ?TYPE_BLOB; T == ?TYPE_TINY_BLOB;
  570. T == ?TYPE_GEOMETRY; T == ?TYPE_JSON ->
  571. %% As of MySQL 5.6.21 we receive SET and ENUM values as STRING, i.e. we
  572. %% cannot convert them to atom() or sets:set(), etc.
  573. lenenc_str(Data);
  574. decode_binary(#col{type = ?TYPE_LONGLONG},
  575. <<Value:64/signed-little, Rest/binary>>) ->
  576. {Value, Rest};
  577. decode_binary(#col{type = T}, <<Value:32/signed-little, Rest/binary>>)
  578. when T == ?TYPE_LONG; T == ?TYPE_INT24 ->
  579. {Value, Rest};
  580. decode_binary(#col{type = T}, <<Value:16/signed-little, Rest/binary>>)
  581. when T == ?TYPE_SHORT; T == ?TYPE_YEAR ->
  582. {Value, Rest};
  583. decode_binary(#col{type = ?TYPE_TINY}, <<Value:8/signed, Rest/binary>>) ->
  584. {Value, Rest};
  585. decode_binary(#col{type = T, decimals = S, length = L}, Data)
  586. when T == ?TYPE_DECIMAL; T == ?TYPE_NEWDECIMAL ->
  587. %% Length is the max number of symbols incl. dot and minus sign, e.g. the
  588. %% number of digits plus 2.
  589. {Binary, Rest} = lenenc_str(Data),
  590. {decode_decimal(Binary, L - 2, S), Rest};
  591. decode_binary(#col{type = ?TYPE_DOUBLE},
  592. <<Value:64/float-little, Rest/binary>>) ->
  593. {Value, Rest};
  594. decode_binary(#col{type = ?TYPE_FLOAT}, <<0.0:32/float-little, Rest/binary>>) ->
  595. %% TYPE_FLOAT conversation fails on math:log10(0.0)
  596. {0.0, Rest};
  597. decode_binary(#col{type = ?TYPE_FLOAT},
  598. <<Value:32/float-little, Rest/binary>>) ->
  599. %% There is a precision loss when storing and fetching a 32-bit float.
  600. %% In the text protocol, it is obviously rounded. Storing 3.14 in a FLOAT
  601. %% column and fetching it using the text protocol, we get "3.14" which we
  602. %% parse to the Erlang double as close as possible to 3.14. Fetching the
  603. %% same value as a binary 32-bit float, we get 3.140000104904175. To achieve
  604. %% the same rounding after receiving it as a 32-bit float, we try to do the
  605. %% same rounding here as MySQL does when sending it over the text protocol.
  606. %%
  607. %% This comment explains the idea:
  608. %%
  609. %% Posted by Geoffrey Downs on March 10 2011 10:26am
  610. %%
  611. %% Following up... I *think* this is correct for the default float
  612. %% columns in mysql:
  613. %%
  614. %% var yourNumber = some floating point value
  615. %% max decimal precision = 10 ^ (-5 + floor(yourNumber log 10))
  616. %% So:
  617. %% 0 < x < 10 -> max precision is 0.00001
  618. %% 10 <= x < 100 -> max precision is 0.0001
  619. %% 100 <= x < 1000 -> max precision is 0.001
  620. %% etc.
  621. %%
  622. %% (From http://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html
  623. %% fetched 10 Nov 2014)
  624. %%
  625. %% The above is almost correct, except for the example in the interval
  626. %% 0 < x < 1. There are 6 significant digits also for these numbers.
  627. %%
  628. %% Now, instead of P = 0.00001 we want the inverse 100000.0 but if we
  629. %% compute Factor = 1 / P we get a precision loss, so instead we do this:
  630. Factor = math:pow(10, floor(6 - math:log10(abs(Value)))),
  631. RoundedValue = round(Value * Factor) / Factor,
  632. {RoundedValue, Rest};
  633. decode_binary(#col{type = ?TYPE_BIT, length = Length}, Data) ->
  634. {Binary, Rest} = lenenc_str(Data),
  635. %% Convert to <<_:Length/bitstring>>
  636. {decode_bitstring(Binary, Length), Rest};
  637. decode_binary(#col{type = ?TYPE_DATE}, <<Length, Data/binary>>) ->
  638. %% Coded in the same way as DATETIME and TIMESTAMP below, but returned in
  639. %% a simple triple.
  640. case {Length, Data} of
  641. {0, _} -> {{0, 0, 0}, Data};
  642. {4, <<Y:16/little, M, D, Rest/binary>>} -> {{Y, M, D}, Rest}
  643. end;
  644. decode_binary(#col{type = T}, <<Length, Data/binary>>)
  645. when T == ?TYPE_DATETIME; T == ?TYPE_TIMESTAMP ->
  646. %% length (1) -- number of bytes following (valid values: 0, 4, 7, 11)
  647. case {Length, Data} of
  648. {0, _} ->
  649. {{{0, 0, 0}, {0, 0, 0}}, Data};
  650. {4, <<Y:16/little, M, D, Rest/binary>>} ->
  651. {{{Y, M, D}, {0, 0, 0}}, Rest};
  652. {7, <<Y:16/little, M, D, H, Mi, S, Rest/binary>>} ->
  653. {{{Y, M, D}, {H, Mi, S}}, Rest};
  654. {11, <<Y:16/little, M, D, H, Mi, S, Micro:32/little, Rest/binary>>} ->
  655. {{{Y, M, D}, {H, Mi, S + 0.000001 * Micro}}, Rest}
  656. end;
  657. decode_binary(#col{type = ?TYPE_TIME}, <<Length, Data/binary>>) ->
  658. %% length (1) -- number of bytes following (valid values: 0, 8, 12)
  659. %% is_negative (1) -- (1 if minus, 0 for plus)
  660. %% days (4) -- days
  661. %% hours (1) -- hours
  662. %% minutes (1) -- minutes
  663. %% seconds (1) -- seconds
  664. %% micro_seconds (4) -- micro-seconds
  665. case {Length, Data} of
  666. {0, _} ->
  667. {{0, {0, 0, 0}}, Data};
  668. {8, <<0, D:32/little, H, M, S, Rest/binary>>} ->
  669. {{D, {H, M, S}}, Rest};
  670. {12, <<0, D:32/little, H, M, S, Micro:32/little, Rest/binary>>} ->
  671. {{D, {H, M, S + 0.000001 * Micro}}, Rest};
  672. {8, <<1, D:32/little, H, M, S, Rest/binary>>} ->
  673. %% Negative time. Example: '-00:00:01' --> {-1,{23,59,59}}
  674. Seconds = ((D * 24 + H) * 60 + M) * 60 + S,
  675. %Seconds = D * 86400 + calendar:time_to_seconds({H, M, S}),
  676. {calendar:seconds_to_daystime(-Seconds), Rest};
  677. {12, <<1, D:32/little, H, M, S, Micro:32/little, Rest/binary>>}
  678. when Micro > 0 ->
  679. %% Negate and convert to seconds, excl fractions
  680. Seconds = -(((D * 24 + H) * 60 + M) * 60 + S),
  681. %Seconds = -D * 86400 - calendar:time_to_seconds({H, M, S}),
  682. %% Subtract 1 second for the fractions
  683. {Days, {Hours, Minutes, Sec}} =
  684. calendar:seconds_to_daystime(Seconds - 1),
  685. %% Adding the fractions to Sec again makes it a float
  686. {{Days, {Hours, Minutes, Sec + 1 - 0.000001 * Micro}}, Rest}
  687. end.
  688. %% @doc Like trunc/1 but towards negative infinity instead of towards zero.
  689. floor(Value) ->
  690. Trunc = trunc(Value),
  691. if
  692. Trunc =< Value -> Trunc;
  693. Trunc > Value -> Trunc - 1 %% for negative values
  694. end.
  695. %% @doc Encodes a term reprenting av value as a binary for use in the binary
  696. %% protocol. As this is used to encode parameters for prepared statements, the
  697. %% encoding is in its required form, namely `<<Type:8, Sign:8, Value/binary>>'.
  698. -spec encode_param(term()) -> {TypeAndSign :: binary(), Data :: binary()}.
  699. encode_param(null) ->
  700. {<<?TYPE_NULL, 0>>, <<>>};
  701. encode_param(Value) when is_binary(Value) ->
  702. EncLength = lenenc_int_encode(byte_size(Value)),
  703. {<<?TYPE_VAR_STRING, 0>>, <<EncLength/binary, Value/binary>>};
  704. encode_param(Value) when is_list(Value) ->
  705. encode_param(unicode:characters_to_binary(Value));
  706. encode_param(Value) when is_integer(Value), Value >= 0 ->
  707. %% We send positive integers with the 'unsigned' flag set.
  708. if
  709. Value =< 16#ff ->
  710. {<<?TYPE_TINY, 16#80>>, <<Value:8>>};
  711. Value =< 16#ffff ->
  712. {<<?TYPE_SHORT, 16#80>>, <<Value:16/little>>};
  713. Value =< 16#ffffffff ->
  714. {<<?TYPE_LONG, 16#80>>, <<Value:32/little>>};
  715. Value =< 16#ffffffffffffffff ->
  716. {<<?TYPE_LONGLONG, 16#80>>, <<Value:64/little>>};
  717. true ->
  718. %% If larger than a 64-bit int we send it as a string. MySQL does
  719. %% silently cast strings in aithmetic expressions. Also, DECIMALs
  720. %% are always sent as strings.
  721. encode_param(integer_to_binary(Value))
  722. end;
  723. encode_param(Value) when is_integer(Value), Value < 0 ->
  724. if
  725. Value >= -16#80 ->
  726. {<<?TYPE_TINY, 0>>, <<Value:8>>};
  727. Value >= -16#8000 ->
  728. {<<?TYPE_SHORT, 0>>, <<Value:16/little>>};
  729. Value >= -16#80000000 ->
  730. {<<?TYPE_LONG, 0>>, <<Value:32/little>>};
  731. Value >= -16#8000000000000000 ->
  732. {<<?TYPE_LONGLONG, 0>>, <<Value:64/little>>};
  733. true ->
  734. encode_param(integer_to_binary(Value))
  735. end;
  736. encode_param(Value) when is_float(Value) ->
  737. {<<?TYPE_DOUBLE, 0>>, <<Value:64/float-little>>};
  738. encode_param(Value) when is_bitstring(Value) ->
  739. Binary = encode_bitstring(Value),
  740. EncLength = lenenc_int_encode(byte_size(Binary)),
  741. {<<?TYPE_VAR_STRING, 0>>, <<EncLength/binary, Binary/binary>>};
  742. encode_param({Y, M, D}) ->
  743. %% calendar:date()
  744. {<<?TYPE_DATE, 0>>, <<4, Y:16/little, M, D>>};
  745. encode_param({{Y, M, D}, {0, 0, 0}}) ->
  746. %% Datetime at midnight
  747. {<<?TYPE_DATETIME, 0>>, <<4, Y:16/little, M, D>>};
  748. encode_param({{Y, M, D}, {H, Mi, S}}) when is_integer(S) ->
  749. %% calendar:datetime()
  750. {<<?TYPE_DATETIME, 0>>, <<7, Y:16/little, M, D, H, Mi, S>>};
  751. encode_param({{Y, M, D}, {H, Mi, S}}) when is_float(S) ->
  752. %% calendar:datetime() with a float for seconds. This way it looks very
  753. %% similar to a datetime. Microseconds in MySQL timestamps are possible but
  754. %% not very common.
  755. Sec = trunc(S),
  756. Micro = round(1000000 * (S - Sec)),
  757. {<<?TYPE_DATETIME, 0>>, <<11, Y:16/little, M, D, H, Mi, Sec,
  758. Micro:32/little>>};
  759. encode_param({D, {H, M, S}}) when is_integer(S), D >= 0 ->
  760. %% calendar:seconds_to_daystime()
  761. {<<?TYPE_TIME, 0>>, <<8, 0, D:32/little, H, M, S>>};
  762. encode_param({D, {H, M, S}}) when is_integer(S), D < 0 ->
  763. %% Convert to seconds, negate and convert back to daystime form.
  764. %% Then set the minus flag.
  765. Seconds = ((D * 24 + H) * 60 + M) * 60 + S,
  766. {D1, {H1, M1, S1}} = calendar:seconds_to_daystime(-Seconds),
  767. {<<?TYPE_TIME, 0>>, <<8, 1, D1:32/little, H1, M1, S1>>};
  768. encode_param({D, {H, M, S}}) when is_float(S), D >= 0 ->
  769. S1 = trunc(S),
  770. Micro = round(1000000 * (S - S1)),
  771. {<<?TYPE_TIME, 0>>, <<12, 0, D:32/little, H, M, S1, Micro:32/little>>};
  772. encode_param({D, {H, M, S}}) when is_float(S), S > 0.0, D < 0 ->
  773. IntS = trunc(S),
  774. Micro = round(1000000 * (1 - S + IntS)),
  775. Seconds = (D * 24 + H) * 3600 + M * 60 + IntS + 1,
  776. {D1, {M1, H1, S1}} = calendar:seconds_to_daystime(-Seconds),
  777. {<<?TYPE_TIME, 0>>, <<12, 1, D1:32/little, H1, M1, S1, Micro:32/little>>};
  778. encode_param({D, {H, M, 0.0}}) ->
  779. encode_param({D, {H, M, 0}}).
  780. %% -- Value representation in both the text and binary protocols --
  781. %% @doc Convert to `<<_:Length/bitstring>>'
  782. decode_bitstring(Binary, Length) ->
  783. PaddingLength = bit_size(Binary) - Length,
  784. <<_:PaddingLength/bitstring, Bitstring:Length/bitstring>> = Binary,
  785. Bitstring.
  786. encode_bitstring(Bitstring) ->
  787. Size = bit_size(Bitstring),
  788. PaddingSize = byte_size(Bitstring) * 8 - Size,
  789. <<0:PaddingSize, Bitstring:Size/bitstring>>.
  790. decode_decimal(Bin, _P, 0) ->
  791. binary_to_integer(Bin);
  792. decode_decimal(Bin, P, S) when P =< 15, S > 0 ->
  793. binary_to_float(Bin);
  794. decode_decimal(Bin, P, S) when P >= 16, S > 0 ->
  795. Bin.
  796. %% -- Protocol basics: packets --
  797. %% @doc Wraps Data in packet headers, sends it by calling TcpModule:send/2 with
  798. %% Socket and returns {ok, SeqNum1} where SeqNum1 is the next sequence number.
  799. -spec send_packet(atom(), term(), Data :: binary(), SeqNum :: integer()) ->
  800. {ok, NextSeqNum :: integer()}.
  801. send_packet(TcpModule, Socket, Data, SeqNum) ->
  802. {WithHeaders, SeqNum1} = add_packet_headers(Data, SeqNum),
  803. ok = TcpModule:send(Socket, WithHeaders),
  804. {ok, SeqNum1}.
  805. %% @see recv_packet/4
  806. recv_packet(TcpModule, Socket, SeqNum) ->
  807. recv_packet(TcpModule, Socket, infinity, SeqNum).
  808. %% @doc Receives data by calling TcpModule:recv/2 and removes the packet
  809. %% headers. Returns the packet contents and the next packet sequence number.
  810. -spec recv_packet(atom(), term(), timeout(), integer() | any) ->
  811. {ok, Data :: binary(), NextSeqNum :: integer()} | {error, term()}.
  812. recv_packet(TcpModule, Socket, Timeout, SeqNum) ->
  813. recv_packet(TcpModule, Socket, Timeout, SeqNum, <<>>).
  814. %% @doc Accumulating helper for recv_packet/4
  815. -spec recv_packet(atom(), term(), timeout(), integer() | any, binary()) ->
  816. {ok, Data :: binary(), NextSeqNum :: integer()} | {error, term()}.
  817. recv_packet(TcpModule, Socket, Timeout, ExpectSeqNum, Acc) ->
  818. case TcpModule:recv(Socket, 4, Timeout) of
  819. {ok, Header} ->
  820. {Size, SeqNum, More} = parse_packet_header(Header),
  821. true = SeqNum == ExpectSeqNum orelse ExpectSeqNum == any,
  822. {ok, Body} = TcpModule:recv(Socket, Size),
  823. Acc1 = <<Acc/binary, Body/binary>>,
  824. NextSeqNum = (SeqNum + 1) band 16#ff,
  825. case More of
  826. false -> {ok, Acc1, NextSeqNum};
  827. true -> recv_packet(TcpModule, Socket, Timeout, NextSeqNum,
  828. Acc1)
  829. end;
  830. {error, Reason} ->
  831. {error, Reason}
  832. end.
  833. %% @doc Parses a packet header (32 bits) and returns a tuple.
  834. %%
  835. %% The client should first read a header and parse it. Then read PacketLength
  836. %% bytes. If there are more packets, read another header and read a new packet
  837. %% length of payload until there are no more packets. The seq num should
  838. %% increment from 0 and may wrap around at 255 back to 0.
  839. %%
  840. %% When all packets are read and the payload of all packets are concatenated, it
  841. %% can be parsed using parse_response/1, etc. depending on what type of response
  842. %% is expected.
  843. -spec parse_packet_header(PackerHeader :: binary()) ->
  844. {PacketLength :: integer(),
  845. SeqNum :: integer(),
  846. MorePacketsExist :: boolean()}.
  847. parse_packet_header(<<PacketLength:24/little-integer, SeqNum:8/integer>>) ->
  848. {PacketLength, SeqNum, PacketLength == 16#ffffff}.
  849. %% @doc Splits a packet body into chunks and wraps them in headers. The
  850. %% resulting list is ready to sent to the socket.
  851. -spec add_packet_headers(PacketBody :: iodata(), SeqNum :: integer()) ->
  852. {PacketWithHeaders :: iodata(), NextSeqNum :: integer()}.
  853. add_packet_headers(PacketBody, SeqNum) ->
  854. Bin = iolist_to_binary(PacketBody),
  855. Size = size(Bin),
  856. SeqNum1 = (SeqNum + 1) band 16#ff,
  857. %% Todo: implement the case when Size >= 16#ffffff.
  858. if Size < 16#ffffff ->
  859. {[<<Size:24/little, SeqNum:8>>, Bin], SeqNum1}
  860. end.
  861. -spec parse_ok_packet(binary()) -> #ok{}.
  862. parse_ok_packet(<<?OK:8, Rest/binary>>) ->
  863. {AffectedRows, Rest1} = lenenc_int(Rest),
  864. {InsertId, Rest2} = lenenc_int(Rest1),
  865. <<StatusFlags:16/little, WarningCount:16/little, Msg/binary>> = Rest2,
  866. %% We have CLIENT_PROTOCOL_41 but not CLIENT_SESSION_TRACK enabled. The
  867. %% protocol is conditional. This is from the protocol documentation:
  868. %%
  869. %% if capabilities & CLIENT_PROTOCOL_41 {
  870. %% int<2> status_flags
  871. %% int<2> warning_count
  872. %% } elseif capabilities & CLIENT_TRANSACTIONS {
  873. %% int<2> status_flags
  874. %% }
  875. %% if capabilities & CLIENT_SESSION_TRACK {
  876. %% string<lenenc> info
  877. %% if status_flags & SERVER_SESSION_STATE_CHANGED {
  878. %% string<lenenc> session_state_changes
  879. %% }
  880. %% } else {
  881. %% string<EOF> info
  882. %% }
  883. #ok{affected_rows = AffectedRows,
  884. insert_id = InsertId,
  885. status = StatusFlags,
  886. warning_count = WarningCount,
  887. msg = Msg}.
  888. -spec parse_error_packet(binary()) -> #error{}.
  889. parse_error_packet(<<?ERROR:8, ErrNo:16/little, "#", SQLState:5/binary-unit:8,
  890. Msg/binary>>) ->
  891. %% Error, 4.1 protocol.
  892. %% (Older protocol: <<?ERROR:8, ErrNo:16/little, Msg/binary>>)
  893. #error{code = ErrNo, state = SQLState, msg = Msg}.
  894. -spec parse_eof_packet(binary()) -> #eof{}.
  895. parse_eof_packet(<<?EOF:8, NumWarnings:16/little, StatusFlags:16/little>>) ->
  896. %% EOF packet, 4.1 protocol.
  897. %% (Older protocol: <<?EOF:8>>)
  898. #eof{status = StatusFlags, warning_count = NumWarnings}.
  899. -spec hash_password(Password :: iodata(), Salt :: binary()) -> Hash :: binary().
  900. hash_password(Password, Salt) ->
  901. %% From the "MySQL Internals" manual:
  902. %% SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat>
  903. %% SHA1( SHA1( password ) ) )
  904. %% ----
  905. %% Make sure the salt is exactly 20 bytes.
  906. %%
  907. %% The auth data is obviously nul-terminated. For the "native" auth
  908. %% method, it should be a 20 byte salt, so let's trim it in this case.
  909. PasswordBin = case erlang:is_binary(Password) of
  910. true -> Password;
  911. false -> erlang:iolist_to_binary(Password)
  912. end,
  913. case PasswordBin =:= <<>> of
  914. true -> <<>>;
  915. false -> hash_non_empty_password(Password, Salt)
  916. end.
  917. -spec hash_non_empty_password(Password :: iodata(), Salt :: binary()) -> Hash :: binary().
  918. hash_non_empty_password(Password, Salt) ->
  919. Salt1 = case Salt of
  920. <<SaltNoNul:20/binary-unit:8, 0>> -> SaltNoNul;
  921. _ when size(Salt) == 20 -> Salt
  922. end,
  923. %% Hash as described above.
  924. <<Hash1Num:160>> = Hash1 = crypto:hash(sha, Password),
  925. Hash2 = crypto:hash(sha, Hash1),
  926. <<Hash3Num:160>> = crypto:hash(sha, <<Salt1/binary, Hash2/binary>>),
  927. <<(Hash1Num bxor Hash3Num):160>>.
  928. %% --- Lowlevel: variable length integers and strings ---
  929. %% lenenc_int/1 decodes length-encoded-integer values
  930. -spec lenenc_int(Input :: binary()) -> {Value :: integer(), Rest :: binary()}.
  931. lenenc_int(<<Value:8, Rest/bits>>) when Value < 251 -> {Value, Rest};
  932. lenenc_int(<<16#fc:8, Value:16/little, Rest/binary>>) -> {Value, Rest};
  933. lenenc_int(<<16#fd:8, Value:24/little, Rest/binary>>) -> {Value, Rest};
  934. lenenc_int(<<16#fe:8, Value:64/little, Rest/binary>>) -> {Value, Rest}.
  935. %% Length-encoded-integer encode. Appends the encoded value to Acc.
  936. %% Values not representable in 64 bits are not accepted.
  937. -spec lenenc_int_encode(0..16#ffffffffffffffff) -> binary().
  938. lenenc_int_encode(Value) when Value >= 0 ->
  939. if Value < 251 -> <<Value>>;
  940. Value =< 16#ffff -> <<16#fc, Value:16/little>>;
  941. Value =< 16#ffffff -> <<16#fd, Value:24/little>>;
  942. Value =< 16#ffffffffffffffff -> <<16#fe, Value:64/little>>
  943. end.
  944. %% lenenc_str/1 decodes length-encoded-string values
  945. -spec lenenc_str(Input :: binary()) -> {String :: binary(), Rest :: binary()}.
  946. lenenc_str(Bin) ->
  947. {Length, Rest} = lenenc_int(Bin),
  948. <<String:Length/binary, Rest1/binary>> = Rest,
  949. {String, Rest1}.
  950. %% nts/1 decodes a nul-terminated string
  951. -spec nulterm_str(Input :: binary()) -> {String :: binary(), Rest :: binary()}.
  952. nulterm_str(Bin) ->
  953. [String, Rest] = binary:split(Bin, <<0>>),
  954. {String, Rest}.
  955. -ifdef(TEST).
  956. -include_lib("eunit/include/eunit.hrl").
  957. %% Testing some of the internal functions, mostly the cases we don't cover in
  958. %% other tests.
  959. decode_text_test() ->
  960. %% Int types
  961. lists:foreach(fun (T) ->
  962. ?assertEqual(1, decode_text(#col{type = T}, <<"1">>))
  963. end,
  964. [?TYPE_TINY, ?TYPE_SHORT, ?TYPE_LONG, ?TYPE_LONGLONG,
  965. ?TYPE_INT24, ?TYPE_YEAR]),
  966. %% BIT
  967. <<217>> = decode_text(#col{type = ?TYPE_BIT, length = 8}, <<217>>),
  968. %% Floating point and decimal numbers
  969. lists:foreach(fun (T) ->
  970. ?assertEqual(3.0, decode_text(#col{type = T}, <<"3.0">>))
  971. end,
  972. [?TYPE_FLOAT, ?TYPE_DOUBLE]),
  973. %% Decimal types
  974. lists:foreach(fun (T) ->
  975. ColDef = #col{type = T, decimals = 1, length = 4},
  976. ?assertMatch(3.0, decode_text(ColDef, <<"3.0">>))
  977. end,
  978. [?TYPE_DECIMAL, ?TYPE_NEWDECIMAL]),
  979. ?assertEqual(3.0, decode_text(#col{type = ?TYPE_FLOAT}, <<"3">>)),
  980. ?assertEqual(30.0, decode_text(#col{type = ?TYPE_FLOAT}, <<"3e1">>)),
  981. ?assertEqual(3, decode_text(#col{type = ?TYPE_LONG}, <<"3">>)),
  982. %% Date and time
  983. ?assertEqual({2014, 11, 01},
  984. decode_text(#col{type = ?TYPE_DATE}, <<"2014-11-01">>)),
  985. ?assertEqual({0, {23, 59, 01}},
  986. decode_text(#col{type = ?TYPE_TIME}, <<"23:59:01">>)),
  987. ?assertEqual({{2014, 11, 01}, {23, 59, 01}},
  988. decode_text(#col{type = ?TYPE_DATETIME},
  989. <<"2014-11-01 23:59:01">>)),
  990. ?assertEqual({{2014, 11, 01}, {23, 59, 01}},
  991. decode_text(#col{type = ?TYPE_TIMESTAMP},
  992. <<"2014-11-01 23:59:01">>)),
  993. %% Strings and blobs
  994. lists:foreach(fun (T) ->
  995. ColDef = #col{type = T},
  996. ?assertEqual(<<"x">>, decode_text(ColDef, <<"x">>))
  997. end,
  998. [?TYPE_VARCHAR, ?TYPE_ENUM, ?TYPE_TINY_BLOB,
  999. ?TYPE_MEDIUM_BLOB, ?TYPE_LONG_BLOB, ?TYPE_BLOB,
  1000. ?TYPE_VAR_STRING, ?TYPE_STRING, ?TYPE_GEOMETRY]),
  1001. ok.
  1002. decode_binary_test() ->
  1003. %% Test the special rounding we apply to (single precision) floats.
  1004. ?assertEqual({1.0, <<>>},
  1005. decode_binary(#col{type = ?TYPE_FLOAT},
  1006. <<1.0:32/float-little>>)),
  1007. ?assertEqual({0.2, <<>>},
  1008. decode_binary(#col{type = ?TYPE_FLOAT},
  1009. <<0.2:32/float-little>>)),
  1010. ?assertEqual({-33.3333, <<>>},
  1011. decode_binary(#col{type = ?TYPE_FLOAT},
  1012. <<-33.333333:32/float-little>>)),
  1013. ?assertEqual({0.000123457, <<>>},
  1014. decode_binary(#col{type = ?TYPE_FLOAT},
  1015. <<0.00012345678:32/float-little>>)),
  1016. ?assertEqual({1234.57, <<>>},
  1017. decode_binary(#col{type = ?TYPE_FLOAT},
  1018. <<1234.56789:32/float-little>>)),
  1019. ok.
  1020. null_bitmap_test() ->
  1021. ?assertEqual({<<0, 1:1>>, <<>>}, null_bitmap_decode(9, <<0, 4>>, 2)),
  1022. ?assertEqual(<<0, 4>>, null_bitmap_encode(<<0, 1:1>>, 2)),
  1023. ok.
  1024. lenenc_int_test() ->
  1025. %% decode
  1026. ?assertEqual({40, <<>>}, lenenc_int(<<40>>)),
  1027. ?assertEqual({16#ff, <<>>}, lenenc_int(<<16#fc, 255, 0>>)),
  1028. ?assertEqual({16#33aaff, <<>>}, lenenc_int(<<16#fd, 16#ff, 16#aa, 16#33>>)),
  1029. ?assertEqual({16#12345678, <<>>}, lenenc_int(<<16#fe, 16#78, 16#56, 16#34,
  1030. 16#12, 0, 0, 0, 0>>)),
  1031. %% encode
  1032. ?assertEqual(<<40>>, lenenc_int_encode(40)),
  1033. ?assertEqual(<<16#fc, 255, 0>>, lenenc_int_encode(255)),
  1034. ?assertEqual(<<16#fd, 16#ff, 16#aa, 16#33>>,
  1035. lenenc_int_encode(16#33aaff)),
  1036. ?assertEqual(<<16#fe, 16#78, 16#56, 16#34, 16#12, 0, 0, 0, 0>>,
  1037. lenenc_int_encode(16#12345678)),
  1038. ok.
  1039. lenenc_str_test() ->
  1040. ?assertEqual({<<"Foo">>, <<"bar">>}, lenenc_str(<<3, "Foobar">>)).
  1041. nulterm_test() ->
  1042. ?assertEqual({<<"Foo">>, <<"bar">>}, nulterm_str(<<"Foo", 0, "bar">>)).
  1043. parse_header_test() ->
  1044. %% Example from "MySQL Internals", revision 307, section 14.1.3.3 EOF_Packet
  1045. Packet = <<16#05, 16#00, 16#00, 16#05, 16#fe, 16#00, 16#00, 16#02, 16#00>>,
  1046. <<Header:4/binary-unit:8, Body/binary>> = Packet,
  1047. %% Check header contents and body length
  1048. ?assertEqual({size(Body), 5, false}, parse_packet_header(Header)),
  1049. ok.
  1050. add_packet_headers_test() ->
  1051. {Data, 43} = add_packet_headers(<<"foo">>, 42),
  1052. ?assertEqual(<<3, 0, 0, 42, "foo">>, list_to_binary(Data)).
  1053. parse_ok_test() ->
  1054. Body = <<0, 5, 1, 2, 0, 0, 0, "Foo">>,
  1055. ?assertEqual(#ok{affected_rows = 5,
  1056. insert_id = 1,
  1057. status = ?SERVER_STATUS_AUTOCOMMIT,
  1058. warning_count = 0,
  1059. msg = <<"Foo">>},
  1060. parse_ok_packet(Body)).
  1061. parse_error_test() ->
  1062. %% Protocol 4.1
  1063. Body = <<255, 42, 0, "#", "XYZxx", "Foo">>,
  1064. ?assertEqual(#error{code = 42, state = <<"XYZxx">>, msg = <<"Foo">>},
  1065. parse_error_packet(Body)),
  1066. ok.
  1067. parse_eof_test() ->
  1068. %% Example from "MySQL Internals", revision 307, section 14.1.3.3 EOF_Packet
  1069. Packet = <<16#05, 16#00, 16#00, 16#05, 16#fe, 16#00, 16#00, 16#02, 16#00>>,
  1070. <<_Header:4/binary-unit:8, Body/binary>> = Packet,
  1071. %% Ignore header. Parse body as an eof_packet.
  1072. ?assertEqual(#eof{warning_count = 0,
  1073. status = ?SERVER_STATUS_AUTOCOMMIT},
  1074. parse_eof_packet(Body)),
  1075. ok.
  1076. hash_password_test() ->
  1077. ?assertEqual(<<222,207,222,139,41,181,202,13,191,241,
  1078. 234,234,73,127,244,101,205,3,28,251>>,
  1079. hash_password(<<"foo">>, <<"abcdefghijklmnopqrst">>)),
  1080. ?assertEqual(<<>>, hash_password(<<>>, <<"abcdefghijklmnopqrst">>)).
  1081. -endif.