Skip to content

Commit cd0f985

Browse files
authored
.Net MEVD Sqlite: Escaping (#11534)
Escaping and quoting everything and everywhere for Sqlite connector: - escape the table names when we compute them for the first time, then always use escaped values - escape the storage names when we compute them, so all usages of .StorageName are safe - quote table and column names everywhere - conformance tests + fixes Contributes to #11154
1 parent 4d75e8a commit cd0f985

14 files changed

Lines changed: 414 additions & 270 deletions

‎dotnet/src/Connectors/Connectors.Memory.Sqlite/Conditions/SqliteWhereCondition.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,6 @@ internal abstract class SqliteWhereCondition(string operand, List<object> values
1515
public abstract string BuildQuery(List<string> parameterNames);
1616

1717
protected string GetOperand() => !string.IsNullOrWhiteSpace(this.TableName) ?
18-
$"{this.TableName}.{this.Operand}" :
19-
this.Operand;
18+
$"\"{this.TableName}\".\"{this.Operand}\"" :
19+
$"\"{this.Operand}\"";
2020
}

‎dotnet/src/Connectors/Connectors.Memory.Sqlite/SqliteConstants.cs‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,9 @@ internal static class SqliteConstants
2525
SupportedKeyPropertyTypes = SqliteConstants.SupportedKeyTypes,
2626
SupportedDataPropertyTypes = SqliteConstants.SupportedDataTypes,
2727
SupportedEnumerableDataPropertyElementTypes = [],
28-
SupportedVectorPropertyTypes = SqliteConstants.SupportedVectorTypes
28+
SupportedVectorPropertyTypes = SqliteConstants.SupportedVectorTypes,
29+
30+
EscapeIdentifier = SqliteVectorStoreCollectionCommandBuilder.EscapeIdentifier
2931
};
3032

3133
/// <summary>A <see cref="HashSet{T}"/> of types that a key on the provided model may have.</summary>

‎dotnet/src/Connectors/Connectors.Memory.Sqlite/SqliteVectorStoreCollectionCommandBuilder.cs‎

Lines changed: 136 additions & 91 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,10 @@ namespace Microsoft.SemanticKernel.Connectors.Sqlite;
1919
[SuppressMessage("Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "User input is passed using command parameters.")]
2020
internal static class SqliteVectorStoreCollectionCommandBuilder
2121
{
22+
internal const string DistancePropertyName = "distance";
23+
24+
internal static string EscapeIdentifier(this string value) => value.Replace("'", "''").Replace("\"", "\"\"");
25+
2226
public static DbCommand BuildTableCountCommand(SqliteConnection connection, string tableName)
2327
{
2428
Verify.NotNullOrWhiteSpace(tableName);
@@ -41,16 +45,16 @@ public static DbCommand BuildCreateTableCommand(SqliteConnection connection, str
4145
{
4246
var builder = new StringBuilder();
4347

44-
builder.AppendLine($"""CREATE TABLE {(ifNotExists ? "IF NOT EXISTS " : string.Empty)}"{tableName.Replace("\"", "\"\"")}" (""");
48+
builder.AppendLine($"CREATE TABLE {(ifNotExists ? "IF NOT EXISTS " : string.Empty)}\"{tableName}\" (");
4549

46-
builder.AppendLine(string.Join(",\n", columns.Select(GetColumnDefinition)));
47-
builder.Append(");");
50+
builder.AppendLine(string.Join(",\n", columns.Select(column => GetColumnDefinition(column, quote: true))));
51+
builder.AppendLine(");");
4852

4953
foreach (var column in columns)
5054
{
5155
if (column.HasIndex)
5256
{
53-
builder.AppendLine($"CREATE INDEX {(ifNotExists ? "IF NOT EXISTS " : string.Empty)}{tableName}_{column.Name}_index ON {tableName}({column.Name});");
57+
builder.AppendLine($"CREATE INDEX {(ifNotExists ? "IF NOT EXISTS " : string.Empty)}\"{tableName}_{column.Name}_index\" ON \"{tableName}\"(\"{column.Name}\");");
5458
}
5559
}
5660

@@ -70,9 +74,10 @@ public static DbCommand BuildCreateVirtualTableCommand(
7074
{
7175
var builder = new StringBuilder();
7276

73-
builder.AppendLine($"CREATE VIRTUAL TABLE {(ifNotExists ? "IF NOT EXISTS " : string.Empty)}'{tableName.Replace("'", "''")}' USING {extensionName}(");
77+
builder.AppendLine($"CREATE VIRTUAL TABLE {(ifNotExists ? "IF NOT EXISTS " : string.Empty)}\"{tableName}\" USING {extensionName}(");
7478

75-
builder.AppendLine(string.Join(",\n", columns.Select(GetColumnDefinition)));
79+
// The vector extension is currently uncapable of handling quoted identifiers.
80+
builder.AppendLine(string.Join(",\n", columns.Select(column => GetColumnDefinition(column, quote: false))));
7681
builder.Append(");");
7782

7883
var command = connection.CreateCommand();
@@ -84,7 +89,7 @@ public static DbCommand BuildCreateVirtualTableCommand(
8489

8590
public static DbCommand BuildDropTableCommand(SqliteConnection connection, string tableName)
8691
{
87-
string query = $"DROP TABLE IF EXISTS [{tableName}];";
92+
string query = $"DROP TABLE IF EXISTS \"{tableName}\";";
8893

8994
var command = connection.CreateCommand();
9095

@@ -97,8 +102,9 @@ public static DbCommand BuildInsertCommand(
97102
SqliteConnection connection,
98103
string tableName,
99104
string rowIdentifier,
100-
IReadOnlyList<string> columnNames,
105+
IReadOnlyList<VectorStoreRecordPropertyModel> properties,
101106
IReadOnlyList<Dictionary<string, object?>> records,
107+
bool data,
102108
bool replaceIfExists = false)
103109
{
104110
var builder = new StringBuilder();
@@ -111,11 +117,12 @@ public static DbCommand BuildInsertCommand(
111117
var rowIdentifierParameterName = GetParameterName(rowIdentifier, recordIndex);
112118

113119
var (columns, parameters, values) = GetQueryParts(
114-
columnNames,
120+
properties,
115121
records[recordIndex],
116-
recordIndex);
122+
recordIndex,
123+
data);
117124

118-
builder.AppendLine($"INSERT{replacePlaceholder} INTO {tableName} ({string.Join(", ", columns)})");
125+
builder.AppendLine($"INSERT{replacePlaceholder} INTO \"{tableName}\" ({string.Join(", ", columns)})");
119126
builder.AppendLine($"VALUES ({string.Join(", ", parameters)})");
120127
builder.AppendLine($"RETURNING {rowIdentifier};");
121128

@@ -130,154 +137,187 @@ public static DbCommand BuildInsertCommand(
130137
return command;
131138
}
132139

133-
public static DbCommand BuildSelectCommand(
140+
public static DbCommand BuildSelectDataCommand<TRecord>(
134141
SqliteConnection connection,
135142
string tableName,
136-
IReadOnlyList<string> columnNames,
143+
VectorStoreRecordModel model,
137144
List<SqliteWhereCondition> conditions,
138-
string? orderByPropertyName = null)
145+
GetFilteredRecordOptions<TRecord>? filterOptions = null,
146+
string? extraWhereFilter = null,
147+
Dictionary<string, object>? extraParameters = null,
148+
int top = 0,
149+
int skip = 0)
139150
{
140151
var builder = new StringBuilder();
141152

142-
var (command, whereClause) = GetCommandWithWhereClause(connection, conditions);
153+
var (command, whereClause) = GetCommandWithWhereClause(connection, conditions, extraWhereFilter, extraParameters);
154+
155+
builder.Append("SELECT ");
156+
builder.AppendColumnNames(includeVectors: false, model.Properties);
157+
builder.AppendLine($"FROM \"{tableName}\"");
158+
builder.AppendWhereClause(whereClause);
143159

144-
builder.AppendLine($"SELECT {string.Join(", ", columnNames)}");
145-
builder.AppendLine($"FROM {tableName}");
160+
if (filterOptions is not null)
161+
{
162+
builder.AppendOrderBy(model, filterOptions);
163+
}
146164

147-
AppendWhereClauseIfExists(builder, whereClause);
148-
AppendOrderByIfExists(builder, orderByPropertyName);
165+
builder.AppendLimits(top, skip);
149166

150167
command.CommandText = builder.ToString();
151168

152169
return command;
153170
}
154171

155-
public static DbCommand BuildSelectLeftJoinCommand(
172+
public static DbCommand BuildSelectLeftJoinCommand<TRecord>(
156173
SqliteConnection connection,
157-
string leftTable,
158-
string rightTable,
174+
string vectorTableName,
175+
string dataTableName,
159176
string joinColumnName,
160-
IReadOnlyList<string> leftTablePropertyNames,
161-
IReadOnlyList<string> rightTablePropertyNames,
162-
List<SqliteWhereCondition> conditions,
177+
VectorStoreRecordModel model,
178+
IReadOnlyList<SqliteWhereCondition> conditions,
179+
bool includeDistance,
180+
GetFilteredRecordOptions<TRecord>? filterOptions = null,
163181
string? extraWhereFilter = null,
164182
Dictionary<string, object>? extraParameters = null,
165-
string? orderByPropertyName = null)
183+
int top = 0,
184+
int skip = 0)
166185
{
167186
var builder = new StringBuilder();
168187

169-
List<string> propertyNames =
170-
[
171-
.. leftTablePropertyNames.Select(property => $"{leftTable}.{property}"),
172-
.. rightTablePropertyNames.Select(property => $"{rightTable}.{property}"),
173-
];
174-
175188
var (command, whereClause) = GetCommandWithWhereClause(connection, conditions, extraWhereFilter, extraParameters);
176189

177-
builder.AppendLine($"SELECT {string.Join(", ", propertyNames)}");
178-
builder.AppendLine($"FROM {leftTable} ");
179-
builder.AppendLine($"LEFT JOIN {rightTable} ON {leftTable}.{joinColumnName} = {rightTable}.{joinColumnName}");
190+
builder.Append("SELECT ");
191+
builder.AppendColumnNames(includeVectors: true, model.Properties, vectorTableName, dataTableName);
192+
if (includeDistance)
193+
{
194+
builder.AppendLine($", \"{vectorTableName}\".\"{DistancePropertyName}\"");
195+
}
196+
builder.AppendLine($"FROM \"{vectorTableName}\"");
197+
builder.AppendLine($"LEFT JOIN \"{dataTableName}\" ON \"{vectorTableName}\".\"{joinColumnName}\" = \"{dataTableName}\".\"{joinColumnName}\"");
198+
builder.AppendWhereClause(whereClause);
199+
200+
if (filterOptions is not null)
201+
{
202+
builder.AppendOrderBy(model, filterOptions, dataTableName);
203+
}
204+
else if (includeDistance)
205+
{
206+
builder.AppendLine($"ORDER BY \"{vectorTableName}\".\"{DistancePropertyName}\"");
207+
}
180208

181-
AppendWhereClauseIfExists(builder, whereClause);
182-
AppendOrderByIfExists(builder, orderByPropertyName);
209+
builder.AppendLimits(top, skip);
183210

184211
command.CommandText = builder.ToString();
185212

186213
return command;
187214
}
188215

189-
internal static DbCommand BuildSelectWhereCommand<TRecord>(
190-
VectorStoreRecordModel model,
216+
public static DbCommand BuildDeleteCommand(
191217
SqliteConnection connection,
192-
int top,
193-
GetFilteredRecordOptions<TRecord> options,
194-
string table,
195-
IReadOnlyList<VectorStoreRecordPropertyModel> properties,
196-
string whereFilter,
197-
Dictionary<string, object> whereParameters)
218+
string tableName,
219+
IReadOnlyList<SqliteWhereCondition> conditions)
198220
{
199-
StringBuilder builder = new(200);
221+
var builder = new StringBuilder();
200222

201-
var (command, whereClause) = GetCommandWithWhereClause(connection, Array.Empty<SqliteWhereCondition>(), whereFilter, whereParameters);
223+
var (command, whereClause) = GetCommandWithWhereClause(connection, conditions);
202224

203-
builder.Append("SELECT ");
225+
builder.AppendLine($"DELETE FROM \"{tableName}\"");
226+
builder.AppendWhereClause(whereClause);
227+
228+
command.CommandText = builder.ToString();
229+
230+
return command;
231+
}
232+
233+
#region private
234+
235+
private static StringBuilder AppendColumnNames(this StringBuilder builder, bool includeVectors, IReadOnlyList<VectorStoreRecordPropertyModel> properties,
236+
string? escapedVectorTableName = null, string? escapedDataTableName = null)
237+
{
204238
foreach (var property in properties)
205239
{
206-
if (options.IncludeVectors || property is not VectorStoreRecordVectorPropertyModel)
240+
string? tableName = escapedDataTableName;
241+
if (property is VectorStoreRecordVectorPropertyModel)
242+
{
243+
if (!includeVectors)
244+
{
245+
continue;
246+
}
247+
tableName = escapedVectorTableName;
248+
}
249+
250+
if (tableName is not null)
251+
{
252+
builder.AppendFormat("\"{0}\".\"{1}\",", tableName, property.StorageName);
253+
}
254+
else
207255
{
208256
builder.AppendFormat("\"{0}\",", property.StorageName);
209257
}
210258
}
259+
211260
builder.Length--; // Remove the trailing comma
212261
builder.AppendLine();
262+
return builder;
263+
}
213264

214-
builder.AppendFormat("FROM {0}", table).AppendLine();
215-
builder.AppendFormat("WHERE {0}", whereClause).AppendLine();
216-
265+
private static StringBuilder AppendOrderBy<TRecord>(this StringBuilder builder, VectorStoreRecordModel model,
266+
GetFilteredRecordOptions<TRecord> options, string? tableName = null)
267+
{
217268
if (options.OrderBy.Values.Count > 0)
218269
{
219270
builder.Append("ORDER BY ");
220271

221272
foreach (var sortInfo in options.OrderBy.Values)
222273
{
223-
builder.AppendFormat("[{0}] {1},",
224-
model.GetDataOrKeyProperty(sortInfo.PropertySelector).StorageName,
225-
sortInfo.Ascending ? "ASC" : "DESC");
274+
var storageName = model.GetDataOrKeyProperty(sortInfo.PropertySelector).StorageName;
275+
276+
if (tableName is not null)
277+
{
278+
builder.AppendFormat("\"{0}\".", tableName);
279+
}
280+
281+
builder.AppendFormat("\"{0}\" {1},", storageName, sortInfo.Ascending ? "ASC" : "DESC");
226282
}
227283

228284
builder.Length--; // remove the last comma
229285
builder.AppendLine();
230286
}
231287

232-
builder.AppendFormat("LIMIT {0}", top).AppendLine();
233-
builder.AppendFormat("OFFSET {0}", options.Skip).AppendLine();
234-
235-
command.CommandText = builder.ToString();
236-
237-
return command;
288+
return builder;
238289
}
239290

240-
public static DbCommand BuildDeleteCommand(
241-
SqliteConnection connection,
242-
string tableName,
243-
List<SqliteWhereCondition> conditions)
291+
private static StringBuilder AppendLimits(this StringBuilder builder, int top, int skip)
244292
{
245-
var builder = new StringBuilder();
246-
247-
var (command, whereClause) = GetCommandWithWhereClause(connection, conditions);
248-
249-
builder.AppendLine($"DELETE FROM [{tableName}]");
250-
251-
AppendWhereClauseIfExists(builder, whereClause);
293+
if (top > 0)
294+
{
295+
builder.AppendFormat("LIMIT {0}", top).AppendLine();
296+
}
252297

253-
command.CommandText = builder.ToString();
298+
if (skip > 0)
299+
{
300+
builder.AppendFormat("OFFSET {0}", skip).AppendLine();
301+
}
254302

255-
return command;
303+
return builder;
256304
}
257305

258-
#region private
259-
260-
private static void AppendWhereClauseIfExists(StringBuilder builder, string? whereClause)
306+
private static StringBuilder AppendWhereClause(this StringBuilder builder, string? whereClause)
261307
{
262308
if (!string.IsNullOrWhiteSpace(whereClause))
263309
{
264310
builder.AppendLine($"WHERE {whereClause}");
265311
}
266-
}
267312

268-
private static void AppendOrderByIfExists(StringBuilder builder, string? propertyName)
269-
{
270-
if (!string.IsNullOrWhiteSpace(propertyName))
271-
{
272-
builder.AppendLine($"ORDER BY {propertyName}");
273-
}
313+
return builder;
274314
}
275315

276-
private static string GetColumnDefinition(SqliteColumn column)
316+
private static string GetColumnDefinition(SqliteColumn column, bool quote)
277317
{
278318
const string PrimaryKeyIdentifier = "PRIMARY KEY";
279319

280-
List<string> columnDefinitionParts = [column.Name, column.Type];
320+
List<string> columnDefinitionParts = [quote ? $"\"{column.Name}\"" : column.Name, column.Type];
281321

282322
if (column.IsPrimary)
283323
{
@@ -342,19 +382,24 @@ private static (DbCommand Command, string WhereClause) GetCommandWithWhereClause
342382
}
343383

344384
private static (List<string> Columns, List<string> ParameterNames, List<object?> ParameterValues) GetQueryParts(
345-
IReadOnlyList<string> propertyNames,
385+
IReadOnlyList<VectorStoreRecordPropertyModel> properties,
346386
Dictionary<string, object?> record,
347-
int index)
387+
int index,
388+
bool data)
348389
{
349390
var columns = new List<string>();
350391
var parameterNames = new List<string>();
351392
var parameterValues = new List<object?>();
352393

353-
foreach (var propertyName in propertyNames)
394+
foreach (var property in properties)
354395
{
355-
if (record.TryGetValue(propertyName, out var value))
396+
bool include = property is VectorStoreRecordKeyPropertyModel // The Key column is included in both Vector and Data tables.
397+
|| (data == property is VectorStoreRecordDataPropertyModel); // The Data column is included only in the Data table.
398+
399+
string propertyName = property.StorageName;
400+
if (include && record.TryGetValue(propertyName, out var value))
356401
{
357-
columns.Add(propertyName);
402+
columns.Add($"\"{propertyName}\"");
358403
parameterNames.Add(GetParameterName(propertyName, index));
359404
parameterValues.Add(value ?? DBNull.Value);
360405
}

0 commit comments

Comments
 (0)