comparison libervia/backend/memory/sqla_mapping.py @ 4212:5f2d496c633f

core: get rid of `pickle`: Use of `pickle` to serialise data was a technical legacy that was causing trouble to store in database, to update (if a class was serialised, a change could break update), and to security (pickle can lead to code execution). This patch remove all use of Pickle in favour in JSON, notably: - for caching data, a Pydantic model is now used instead - for SQLAlchemy model, the LegacyPickle is replaced by JSON serialisation - in XEP-0373 a class `PublicKeyMetadata` was serialised. New method `from_dict` and `to_dict` method have been implemented to do serialisation. - new methods to (de)serialise data can now be specified with Identity data types. It is notably used to (de)serialise `path` of avatars. A migration script has been created to convert data (for upgrade or downgrade), with special care for XEP-0373 case. Depending of size of database, this migration script can be long to run. rel 443
author Goffi <goffi@goffi.org>
date Fri, 23 Feb 2024 13:31:04 +0100
parents 2074b2bbe616
children 0d7bb4df2343
comparison
equal deleted inserted replaced
4211:be89ab1cbca4 4212:5f2d496c633f
17 # along with this program. If not, see <http://www.gnu.org/licenses/>. 17 # along with this program. If not, see <http://www.gnu.org/licenses/>.
18 18
19 from datetime import datetime 19 from datetime import datetime
20 import enum 20 import enum
21 import json 21 import json
22 import pickle
23 import time 22 import time
24 from typing import Any, Dict 23 from typing import Any, Dict
25 24
26 from sqlalchemy import ( 25 from sqlalchemy import (
27 Boolean, 26 Boolean,
130 MEDIUM = 20 129 MEDIUM = 20
131 HIGH = 30 130 HIGH = 30
132 URGENT = 40 131 URGENT = 40
133 132
134 133
135 class LegacyPickle(TypeDecorator): 134 class Json(TypeDecorator):
136 """Handle troubles with data pickled by former version of SàT 135 """Handle JSON field in DB independant way"""
137
138 This type is temporary until we do migration to a proper data type
139 """
140 136
141 # Blob is used on SQLite but gives errors when used here, while Text works fine 137 # Blob is used on SQLite but gives errors when used here, while Text works fine
142 impl = Text 138 impl = Text
143 cache_ok = True 139 cache_ok = True
144 140
145 def process_bind_param(self, value, dialect): 141 def process_bind_param(self, value, dialect):
146 if value is None: 142 if value is None:
147 return None 143 return None
148 return pickle.dumps(value, 0) 144 return json.dumps(value, ensure_ascii=False)
149
150 def process_result_value(self, value, dialect):
151 if value is None:
152 return None
153 # value types are inconsistent (probably a consequence of Python 2/3 port
154 # and/or SQLite dynamic typing)
155 try:
156 value = value.encode()
157 except AttributeError:
158 pass
159 # "utf-8" encoding is needed to handle Python 2 pickled data
160 try:
161 return pickle.loads(value, encoding="utf-8")
162 except ModuleNotFoundError:
163 # FIXME: workaround due to package renaming, need to move all pickle code to
164 # JSON
165 return pickle.loads(
166 value.replace(b"sat.plugins", b"libervia.backend.plugins"),
167 encoding="utf-8",
168 )
169
170
171 class Json(TypeDecorator):
172 """Handle JSON field in DB independant way"""
173
174 # Blob is used on SQLite but gives errors when used here, while Text works fine
175 impl = Text
176 cache_ok = True
177
178 def process_bind_param(self, value, dialect):
179 if value is None:
180 return None
181 return json.dumps(value)
182 145
183 def process_result_value(self, value, dialect): 146 def process_result_value(self, value, dialect):
184 if value is None: 147 if value is None:
185 return None 148 return None
186 return json.loads(value) 149 return json.loads(value)
294 name="message_type", 257 name="message_type",
295 create_constraint=True, 258 create_constraint=True,
296 ), 259 ),
297 nullable=False, 260 nullable=False,
298 ) 261 )
299 extra = Column(LegacyPickle) 262 extra = Column(JSON)
300 263
301 profile = relationship("Profile") 264 profile = relationship("Profile")
302 messages = relationship( 265 messages = relationship(
303 "Message", 266 "Message",
304 backref="history", 267 backref="history",
571 class PrivateGenBin(Base): 534 class PrivateGenBin(Base):
572 __tablename__ = "private_gen_bin" 535 __tablename__ = "private_gen_bin"
573 536
574 namespace = Column(Text, primary_key=True) 537 namespace = Column(Text, primary_key=True)
575 key = Column(Text, primary_key=True) 538 key = Column(Text, primary_key=True)
576 value = Column(LegacyPickle) 539 value = Column(JSON)
577 540
578 541
579 class PrivateIndBin(Base): 542 class PrivateIndBin(Base):
580 __tablename__ = "private_ind_bin" 543 __tablename__ = "private_ind_bin"
581 544
582 namespace = Column(Text, primary_key=True) 545 namespace = Column(Text, primary_key=True)
583 key = Column(Text, primary_key=True) 546 key = Column(Text, primary_key=True)
584 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True) 547 profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"), primary_key=True)
585 value = Column(LegacyPickle) 548 value = Column(JSON)
586 549
587 profile = relationship("Profile", back_populates="private_bin_data") 550 profile = relationship("Profile", back_populates="private_bin_data")
588 551
589 552
590 class File(Base): 553 class File(Base):