Skip to content

Commit d4e8274

Browse files
authored
.Net MEVD Document untrusted input in query translators (#12129)
contributes to #11154
1 parent 4854baa commit d4e8274

11 files changed

Lines changed: 55 additions & 48 deletions

File tree

‎dotnet/src/Connectors/Connectors.Memory.AzureAISearch/AzureAISearchFilterTranslator.cs‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -114,8 +114,9 @@ private void GenerateLiteral(object? value)
114114
this._filter.Append(l);
115115
return;
116116

117-
case string s:
118-
this._filter.Append('\'').Append(s.Replace("'", "''")).Append('\''); // TODO: escaping
117+
case string untrustedInput:
118+
// This is the only place where we allow untrusted input to be passed in, so we need to quote and escape it.
119+
this._filter.Append('\'').Append(untrustedInput.Replace("'", "''")).Append('\'');
119120
return;
120121
case bool b:
121122
this._filter.Append(b ? "true" : "false");

‎dotnet/src/Connectors/Connectors.Memory.Common/SqlFilterTranslator.cs‎

Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -61,7 +61,7 @@ protected void Translate(Expression? node, bool isSearchCondition = false)
6161
return;
6262

6363
case QueryParameterExpression { Name: var name, Value: var value }:
64-
this.TranslateQueryParameter(name, value);
64+
this.TranslateQueryParameter(value);
6565
return;
6666

6767
case MemberExpression member:
@@ -154,8 +154,10 @@ protected virtual void TranslateConstant(object? value)
154154
this._sql.Append(l);
155155
return;
156156

157-
case string s:
158-
this._sql.Append('\'').Append(s.Replace("'", "''")).Append('\'');
157+
case string untrustedInput:
158+
// This is the only place where we allow untrusted input to be passed in, so we need to quote and escape it.
159+
// Luckily for us, values are escaped in the same way for every provider that we support so far.
160+
this._sql.Append('\'').Append(untrustedInput.Replace("'", "''")).Append('\'');
159161
return;
160162
case bool b:
161163
this._sql.Append(b ? "TRUE" : "FALSE");
@@ -182,25 +184,26 @@ private void TranslateMember(MemberExpression memberExpression, bool isSearchCon
182184
{
183185
if (this.TryBindProperty(memberExpression, out var property))
184186
{
185-
this.GenerateColumn(property.StorageName, isSearchCondition);
187+
this.GenerateColumn(property, isSearchCondition);
186188
return;
187189
}
188190

189191
throw new NotSupportedException($"Member access for '{memberExpression.Member.Name}' is unsupported - only member access over the filter parameter are supported");
190192
}
191193

192-
protected virtual void GenerateColumn(string column, bool isSearchCondition = false)
193-
=> this._sql.Append('"').Append(column.Replace("\"", "\"\"")).Append('"');
194+
protected virtual void GenerateColumn(PropertyModel property, bool isSearchCondition = false)
195+
// StorageName is considered to be a safe input, we quote and escape it mostly to produce valid SQL.
196+
=> this._sql.Append('"').Append(property.StorageName.Replace("\"", "\"\"")).Append('"');
194197

195-
protected abstract void TranslateQueryParameter(string name, object? value);
198+
protected abstract void TranslateQueryParameter(object? value);
196199

197200
private void TranslateMethodCall(MethodCallExpression methodCall, bool isSearchCondition = false)
198201
{
199202
switch (methodCall)
200203
{
201204
// Dictionary access for dynamic mapping (r => r["SomeString"] == "foo")
202205
case MethodCallExpression when this.TryBindProperty(methodCall, out var property):
203-
this.GenerateColumn(property.StorageName, isSearchCondition);
206+
this.GenerateColumn(property, isSearchCondition);
204207
return;
205208

206209
// Enumerable.Contains()
@@ -302,7 +305,7 @@ private void TranslateUnary(UnaryExpression unary, bool isSearchCondition)
302305

303306
// Handle convert over member access, for dynamic dictionary access (r => (int)r["SomeInt"] == 8)
304307
case ExpressionType.Convert when this.TryBindProperty(unary.Operand, out var property) && unary.Type == property.Type:
305-
this.GenerateColumn(property.StorageName, isSearchCondition);
308+
this.GenerateColumn(property, isSearchCondition);
306309
return;
307310

308311
default:

‎dotnet/src/Connectors/Connectors.Memory.PgVector/PostgresFilterTranslator.cs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -39,7 +39,7 @@ protected override void TranslateContainsOverParameterizedArray(Expression sourc
3939
this._sql.Append(')');
4040
}
4141

42-
protected override void TranslateQueryParameter(string name, object? value)
42+
protected override void TranslateQueryParameter(object? value)
4343
{
4444
// For null values, simply inline rather than parameterize; parameterized NULLs require setting NpgsqlDbType which is a bit more complicated,
4545
// plus in any case equality with NULL requires different SQL (x IS NULL rather than x = y)
@@ -50,6 +50,7 @@ protected override void TranslateQueryParameter(string name, object? value)
5050
else
5151
{
5252
this._parameterValues.Add(value);
53+
// The param name is just the index, so there is no need for escaping or quoting.
5354
this._sql.Append('$').Append(this._parameterIndex++);
5455
}
5556
}

‎dotnet/src/Connectors/Connectors.Memory.SqlServer/SqlServerFilterTranslator.cs‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -45,9 +45,10 @@ protected override void TranslateConstant(object? value)
4545
}
4646
}
4747

48-
protected override void GenerateColumn(string column, bool isSearchCondition = false)
48+
protected override void GenerateColumn(PropertyModel property, bool isSearchCondition = false)
4949
{
50-
this._sql.Append('[').Append(column).Append(']');
50+
// StorageName is considered to be a safe input, we quote and escape it mostly to produce valid SQL.
51+
this._sql.Append('[').Append(property.StorageName.Replace("]", "]]")).Append(']');
5152

5253
// "SELECT * FROM MyTable WHERE BooleanColumn;" is not supported.
5354
// "SELECT * FROM MyTable WHERE BooleanColumn = 1;" is supported.
@@ -88,7 +89,7 @@ protected override void TranslateContainsOverParameterizedArray(Expression sourc
8889
this._sql.Append(')');
8990
}
9091

91-
protected override void TranslateQueryParameter(string name, object? value)
92+
protected override void TranslateQueryParameter(object? value)
9293
{
9394
// For null values, simply inline rather than parameterize; parameterized NULLs require setting NpgsqlDbType which is a bit more complicated,
9495
// plus in any case equality with NULL requires different SQL (x IS NULL rather than x = y)
@@ -99,6 +100,7 @@ protected override void TranslateQueryParameter(string name, object? value)
99100
else
100101
{
101102
this._parameterValues.Add(value);
103+
// The param name is just the index, so there is no need for escaping or quoting.
102104
// SQL Server parameters can't start with a digit (but underscore is OK).
103105
this._sql.Append("@_").Append(this._parameterIndex++);
104106
}

‎dotnet/src/Connectors/Connectors.Memory.SqliteVec/SqliteFilterTranslator.cs‎

Lines changed: 6 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -51,7 +51,7 @@ protected override void TranslateContainsOverParameterizedArray(Expression sourc
5151
this._sql.Append(')');
5252
}
5353

54-
protected override void TranslateQueryParameter(string name, object? value)
54+
protected override void TranslateQueryParameter(object? value)
5555
{
5656
// For null values, simply inline rather than parameterize; parameterized NULLs require setting NpgsqlDbType which is a bit more complicated,
5757
// plus in any case equality with NULL requires different SQL (x IS NULL rather than x = y)
@@ -61,20 +61,11 @@ protected override void TranslateQueryParameter(string name, object? value)
6161
}
6262
else
6363
{
64-
// Duplicate parameter name, create a new parameter with a different name
65-
// TODO: Share the same parameter when it references the same captured value
66-
if (this._parameters.ContainsKey(name))
67-
{
68-
var baseName = name;
69-
var i = 0;
70-
do
71-
{
72-
name = baseName + (i++);
73-
} while (this._parameters.ContainsKey(name));
74-
}
75-
76-
this._parameters.Add(name, value);
77-
this._sql.Append('@').Append(name);
64+
// The param name is just the index, so there is no need for escaping or quoting.
65+
int index = this._sql.Length;
66+
this._sql.Append('@').Append(this._parameters.Count + 1);
67+
string paramName = this._sql.ToString(index, this._sql.Length - index);
68+
this._parameters.Add(paramName, value);
7869
}
7970
}
8071
}

‎dotnet/src/Connectors/Connectors.Memory.SqliteVec/SqliteModelBuilder.cs‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,6 @@ internal class SqliteModelBuilder() : CollectionModelBuilder(s_modelBuildingOpti
1616
RequiresAtLeastOneVector = false,
1717
SupportsMultipleKeys = false,
1818
SupportsMultipleVectors = true,
19-
EscapeIdentifier = SqliteCommandBuilder.EscapeIdentifier
2019
};
2120

2221
protected override bool IsKeyPropertyTypeValid(Type type, [NotNullWhen(false)] out string? supportedTypes)

‎dotnet/src/Connectors/VectorData.Abstractions/ProviderServices/CollectionModelBuilder.cs‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -386,9 +386,7 @@ private void SetPropertyStorageName(PropertyModel property, string? storageName,
386386
return;
387387
}
388388

389-
property.StorageName = this.Options.EscapeIdentifier is not null
390-
? this.Options.EscapeIdentifier(storageName)
391-
: storageName;
389+
property.StorageName = storageName;
392390
}
393391

394392
/// <summary>

‎dotnet/src/Connectors/VectorData.Abstractions/ProviderServices/CollectionModelBuildingOptions.cs‎

Lines changed: 0 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,5 @@
11
// Copyright (c) Microsoft. All rights reserved.
22

3-
using System;
43
using System.Diagnostics.CodeAnalysis;
54

65
namespace Microsoft.Extensions.VectorData.ProviderServices;
@@ -37,9 +36,4 @@ public sealed class CollectionModelBuildingOptions
3736
/// When set, the model builder will manage the key storage name, and users may not customize it.
3837
/// </summary>
3938
public string? ReservedKeyStorageName { get; init; }
40-
41-
/// <summary>
42-
/// A method for escaping storage names.
43-
/// </summary>
44-
public Func<string, string>? EscapeIdentifier { get; init; }
4539
}

‎dotnet/src/IntegrationTests/Connectors/Memory/PgVector/PostgresVectorStoreFixture.cs‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -54,20 +54,20 @@ public NpgsqlConnection GetConnection()
5454

5555
public VectorStoreCollection<TKey, TRecord> GetCollection<TKey, TRecord>(
5656
string collectionName,
57-
VectorStoreCollectionDefinition? recordDefinition = default)
57+
VectorStoreCollectionDefinition? definition = default)
5858
where TKey : notnull
5959
where TRecord : class
6060
{
6161
var vectorStore = this.VectorStore;
62-
return vectorStore.GetCollection<TKey, TRecord>(collectionName, recordDefinition);
62+
return vectorStore.GetCollection<TKey, TRecord>(collectionName, definition);
6363
}
6464

6565
public VectorStoreCollection<object, Dictionary<string, object?>> GetDynamicCollection(
6666
string collectionName,
67-
VectorStoreRecordDefinition recordDefinition)
67+
VectorStoreCollectionDefinition definition)
6868
{
6969
var vectorStore = this.VectorStore;
70-
return vectorStore.GetDynamicCollection(collectionName, recordDefinition);
70+
return vectorStore.GetDynamicCollection(collectionName, definition);
7171
}
7272

7373
/// <summary>

‎dotnet/src/VectorDataIntegrationTests/AzureAISearchIntegrationTests/Support/AzureAISearchAllTypes.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -127,7 +127,7 @@ static void AssertEqual(DateTimeOffset expected, DateTimeOffset actual)
127127
}
128128
}
129129

130-
internal static VectorStoreRecordDefinition GetRecordDefinition()
130+
internal static VectorStoreCollectionDefinition GetRecordDefinition()
131131
=> new()
132132
{
133133
Properties =

0 commit comments

Comments
 (0)