/// <summary>
/// Initializes a new instance of the <see cref="NpgsqlCommand">NpgsqlCommand</see> class with the text of the query, a <see cref="NpgsqlConnection">NpgsqlConnection</see>, and the <see cref="NpgsqlTransaction">NpgsqlTransaction</see>.
/// </summary>
/// <param name="cmdText">The text of the query.</param>
/// <param name="connection">A <see cref="NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
/// <param name="transaction">The <see cref="NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="NpgsqlCommand">NpgsqlCommand</see> executes.</param>
public NpgsqlCommand(string cmdText, [CanBeNull] NpgsqlConnection connection, [CanBeNull] NpgsqlTransaction transaction)
{
GC.SuppressFinalize(this);
_statements = new List <NpgsqlStatement>(1);
_parameters = new NpgsqlParameterCollection();
_commandText = cmdText;
Connection = connection;
Transaction = transaction;
CommandType = CommandType.Text;
}
internal void CloneTo(NpgsqlParameterCollection other)
{
other._internalList.Clear();
foreach (var param in _internalList)
{
var newParam = param.Clone();
newParam.Collection = this;
other._internalList.Add(newParam);
}
other._lookup = _lookup;
other._lookupIgnoreCase = _lookupIgnoreCase;
}
/// <summary>
/// Add range with value
/// </summary>
/// <param name="conn"></param>
/// <param name="values"></param>
public static void AddRangeWithValue(this NpgsqlParameterCollection conn, Dictionary <string, object> values)
{
conn.CheckNull(nameof(conn));
#if NETFRAMEWORK || NETSTANDARD2_0
foreach (var pair in values)
{
var key = pair.Key;
var value = pair.Value;
#else
foreach (var(key, value) in values)
{
#endif
conn.AddWithValue(key, value);
}
}
}
void ParseRawQuery(
ReadOnlySpan <char> sql,
NpgsqlParameterCollection parameters,
List <NpgsqlStatement> statements,
bool deriveParameters)
{
Debug.Assert(deriveParameters == false || parameters.Count == 0);
NpgsqlStatement statement = null !;
var statementIndex = -1;
MoveToNextStatement();
var currCharOfs = 0;
var end = sql.Length;
var ch = '\0';
int dollarTagStart;
int dollarTagEnd;
var currTokenBeg = 0;
var blockCommentLevel = 0;
var parenthesisLevel = 0;
None:
if (currCharOfs >= end)
{
goto Finish;
}
var lastChar = ch;
ch = sql[currCharOfs++];
NoneContinue:
for (; ; lastChar = ch, ch = sql[currCharOfs++])
{
switch (ch)
{
case '/':
goto BlockCommentBegin;
case '-':
goto LineCommentBegin;
case '\'':
goto Quoted;
case '$':
if (!IsIdentifier(lastChar))
{
goto DollarQuotedStart;
}
else
{
break;
}
case '"':
goto DoubleQuoted;
case ':':
if (lastChar != ':')
{
goto ParamStart;
}
else
{
break;
}
case '@':
if (lastChar != '@')
{
goto ParamStart;
}
else
{
break;
}
case ';':
if (parenthesisLevel == 0)
{
goto SemiColon;
}
break;
case '(':
parenthesisLevel++;
break;
case ')':
parenthesisLevel--;
break;
case 'e':
case 'E':
if (!IsLetter(lastChar))
{
goto EscapedStart;
}
else
{
break;
}
}
if (currCharOfs >= end)
{
goto Finish;
}
}
ParamStart:
if (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs];
if (IsParamNameChar(ch))
{
if (currCharOfs - 1 > currTokenBeg)
{
_rewrittenSql.Append(sql.Slice(currTokenBeg, currCharOfs - 1 - currTokenBeg));
}
currTokenBeg = currCharOfs++ - 1;
goto Param;
}
currCharOfs++;
goto NoneContinue;
}
goto Finish;
Param:
// We have already at least one character of the param name
for (;;)
{
lastChar = ch;
if (currCharOfs >= end || !IsParamNameChar(ch = sql[currCharOfs]))
{
var paramName = sql.Slice(currTokenBeg + 1, currCharOfs - (currTokenBeg + 1)).ToString();
if (!_paramIndexMap.TryGetValue(paramName, out var index))
{
// Parameter hasn't been seen before in this query
if (!parameters.TryGetValue(paramName, out var parameter))
{
if (deriveParameters)
{
parameter = new NpgsqlParameter {
ParameterName = paramName
};
parameters.Add(parameter);
}
else
{
// Parameter placeholder does not match a parameter on this command.
// Leave the text as it was in the SQL, it may not be a an actual placeholder
_rewrittenSql.Append(sql.Slice(currTokenBeg, currCharOfs - currTokenBeg));
currTokenBeg = currCharOfs;
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
goto NoneContinue;
}
}
if (!parameter.IsInputDirection)
{
throw new Exception($"Parameter '{paramName}' referenced in SQL but is an out-only parameter");
}
statement.InputParameters.Add(parameter);
index = _paramIndexMap[paramName] = statement.InputParameters.Count;
}
_rewrittenSql.Append('$');
_rewrittenSql.Append(index);
currTokenBeg = currCharOfs;
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
goto NoneContinue;
}
currCharOfs++;
}
Quoted:
while (currCharOfs < end)
{
if (sql[currCharOfs++] == '\'')
{
ch = '\0';
goto None;
}
}
goto Finish;
DoubleQuoted:
while (currCharOfs < end)
{
if (sql[currCharOfs++] == '"')
{
ch = '\0';
goto None;
}
}
goto Finish;
EscapedStart:
if (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs++];
if (ch == '\'')
{
goto Escaped;
}
goto NoneContinue;
}
goto Finish;
Escaped:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
switch (ch)
{
case '\'':
goto MaybeConcatenatedEscaped;
case '\\':
{
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
break;
}
}
}
goto Finish;
MaybeConcatenatedEscaped:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
switch (ch)
{
case '\r':
case '\n':
goto MaybeConcatenatedEscaped2;
case ' ':
case '\t':
case '\f':
continue;
default:
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscaped2:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
switch (ch)
{
case '\'':
goto Escaped;
case '-':
{
if (currCharOfs >= end)
{
goto Finish;
}
ch = sql[currCharOfs++];
if (ch == '-')
{
goto MaybeConcatenatedEscapeAfterComment;
}
lastChar = '\0';
goto NoneContinue;
}
case ' ':
case '\t':
case '\n':
case '\r':
case '\f':
continue;
default:
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscapeAfterComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto MaybeConcatenatedEscaped2;
}
}
goto Finish;
DollarQuotedStart:
if (currCharOfs < end)
{
ch = sql[currCharOfs];
if (ch == '$')
{
// Empty tag
dollarTagStart = dollarTagEnd = currCharOfs;
currCharOfs++;
goto DollarQuoted;
}
if (IsIdentifierStart(ch))
{
dollarTagStart = currCharOfs;
currCharOfs++;
goto DollarQuotedInFirstDelim;
}
lastChar = '$';
currCharOfs++;
goto NoneContinue;
}
goto Finish;
DollarQuotedInFirstDelim:
while (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs++];
if (ch == '$')
{
dollarTagEnd = currCharOfs - 1;
goto DollarQuoted;
}
if (!IsDollarTagIdentifier(ch))
{
goto NoneContinue;
}
}
goto Finish;
DollarQuoted:
var tag = sql.Slice(dollarTagStart - 1, dollarTagEnd - dollarTagStart + 2);
var pos = sql.Slice(dollarTagEnd + 1).IndexOf(tag);
if (pos == -1)
{
currCharOfs = end;
goto Finish;
}
pos += dollarTagEnd + 1; // If the substring is found adjust the position to be relative to the entire span
currCharOfs = pos + dollarTagEnd - dollarTagStart + 2;
ch = '\0';
goto None;
LineCommentBegin:
if (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '-')
{
goto LineComment;
}
lastChar = '\0';
goto NoneContinue;
}
goto Finish;
LineComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto None;
}
}
goto Finish;
BlockCommentBegin:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '*')
{
blockCommentLevel++;
goto BlockComment;
}
if (ch != '/')
{
if (blockCommentLevel > 0)
{
goto BlockComment;
}
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
BlockComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
switch (ch)
{
case '*':
goto BlockCommentEnd;
case '/':
goto BlockCommentBegin;
}
}
goto Finish;
BlockCommentEnd:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '/')
{
if (--blockCommentLevel > 0)
{
goto BlockComment;
}
goto None;
}
if (ch != '*')
{
goto BlockComment;
}
}
goto Finish;
SemiColon:
_rewrittenSql.Append(sql.Slice(currTokenBeg, currCharOfs - currTokenBeg - 1));
statement.SQL = _rewrittenSql.ToString();
while (currCharOfs < end)
{
ch = sql[currCharOfs];
if (char.IsWhiteSpace(ch))
{
currCharOfs++;
continue;
}
// TODO: Handle end of line comment? Although psql doesn't seem to handle them...
currTokenBeg = currCharOfs;
if (_rewrittenSql.Length > 0)
{
MoveToNextStatement();
}
goto None;
}
if (statements.Count > statementIndex + 1)
{
statements.RemoveRange(statementIndex + 1, statements.Count - (statementIndex + 1));
}
return;
Finish:
_rewrittenSql.Append(sql.Slice(currTokenBeg, end - currTokenBeg));
statement.SQL = _rewrittenSql.ToString();
if (statements.Count > statementIndex + 1)
{
statements.RemoveRange(statementIndex + 1, statements.Count - (statementIndex + 1));
}
void MoveToNextStatement()
{
statementIndex++;
if (statements.Count > statementIndex)
{
statement = statements[statementIndex];
statement.Reset();
}
else
{
statement = new NpgsqlStatement();
statements.Add(statement);
}
_paramIndexMap.Clear();
_rewrittenSql.Clear();
}
}
/// <summary>
/// Receives a raw SQL query as passed in by the user, and performs some processing necessary
/// before sending to the backend.
/// This includes doing parameter placeholder processing (@p => $1), and splitting the query
/// up by semicolons if needed (SELECT 1; SELECT 2)
/// </summary>
/// <param name="sql">Raw user-provided query.</param>
/// <param name="parameters">The parameters configured on the <see cref="NpgsqlCommand"/> of this query
/// or an empty <see cref="NpgsqlParameterCollection"/> if deriveParameters is set to true.</param>
/// <param name="statements">An empty list to be populated with the statements parsed by this method</param>
/// <param name="deriveParameters">A bool indicating whether parameters contains a list of preconfigured parameters or an empty list to be filled with derived parameters.</param>
internal void ParseRawQuery(
string sql,
NpgsqlParameterCollection parameters,
List <NpgsqlStatement> statements,
bool deriveParameters = false)
=> ParseRawQuery(sql.AsSpan(), parameters, statements, deriveParameters);
/// <summary>
/// Receives a raw SQL query as passed in by the user, and performs some processing necessary
/// before sending to the backend.
/// This includes doing parameter placebolder processing (@p => $1), and splitting the query
/// up by semicolons if needed (SELECT 1; SELECT 2)
/// </summary>
/// <param name="sql">Raw user-provided query.</param>
/// <param name="standardConformantStrings">Whether the PostgreSQL session is configured to use standard conformant strings.</param>
/// <param name="parameters">The parameters configured on the <see cref="NpgsqlCommand"/> of this query.</param>
/// <param name="queries">An empty list to be populated with the queries parsed by this method</param>
static internal void ParseRawQuery(string sql, bool standardConformantStrings, NpgsqlParameterCollection parameters, List<QueryDetails> queries)
{
Contract.Requires(sql != null);
Contract.Requires(queries != null && !queries.Any());
var currCharOfs = 0;
var end = sql.Length;
var ch = '\0';
var lastChar = '\0';
var dollarTagStart = 0;
var dollarTagEnd = 0;
var currTokenBeg = 0;
var blockCommentLevel = 0;
queries.Clear();
// TODO: Recycle
var paramIndexMap = new Dictionary<string, int>();
var currentSql = new StringWriter();
var currentParameters = new List<NpgsqlParameter>();
None:
if (currCharOfs >= end) {
goto Finish;
}
lastChar = ch;
ch = sql[currCharOfs++];
NoneContinue:
for (; ; lastChar = ch, ch = sql[currCharOfs++]) {
switch (ch) {
case '/':
goto BlockCommentBegin;
case '-':
goto LineCommentBegin;
case '\'':
if (standardConformantStrings)
goto Quoted;
else
goto Escaped;
case '$':
if (!IsIdentifier(lastChar))
goto DollarQuotedStart;
else
break;
case '"':
goto DoubleQuoted;
case ':':
if (lastChar != ':')
goto ParamStart;
else
break;
case '@':
if (lastChar != '@')
goto ParamStart;
else
break;
case ';':
goto SemiColon;
case 'e':
case 'E':
if (!IsLetter(lastChar))
goto EscapedStart;
else
break;
}
if (currCharOfs >= end) {
goto Finish;
}
}
ParamStart:
if (currCharOfs < end) {
lastChar = ch;
ch = sql[currCharOfs];
if (IsParamNameChar(ch)) {
if (currCharOfs - 1 > currTokenBeg) {
currentSql.Write(sql.Substring(currTokenBeg, currCharOfs - 1 - currTokenBeg));
}
currTokenBeg = currCharOfs++ - 1;
goto Param;
} else {
currCharOfs++;
goto NoneContinue;
}
}
goto Finish;
Param:
// We have already at least one character of the param name
for (; ; ) {
lastChar = ch;
if (currCharOfs >= end || !IsParamNameChar(ch = sql[currCharOfs])) {
var paramName = sql.Substring(currTokenBeg, currCharOfs - currTokenBeg);
int index;
if (!paramIndexMap.TryGetValue(paramName, out index)) {
// Parameter hasn't been seen before in this query
NpgsqlParameter parameter;
if (!parameters.TryGetValue(paramName, out parameter)) {
throw new Exception(String.Format("Parameter '{0}' referenced in SQL but not found in parameter list", paramName));
}
if (!parameter.IsInputDirection) {
throw new Exception(String.Format("Parameter '{0}' referenced in SQL but is an out-only parameter", paramName));
}
currentParameters.Add(parameter);
index = paramIndexMap[paramName] = currentParameters.Count;
}
currentSql.Write('$');
currentSql.Write(index);
currTokenBeg = currCharOfs;
if (currCharOfs >= end) {
goto Finish;
}
currCharOfs++;
goto NoneContinue;
} else {
currCharOfs++;
}
}
Quoted:
while (currCharOfs < end) {
if (sql[currCharOfs++] == '\'') {
ch = '\0';
goto None;
}
}
goto Finish;
DoubleQuoted:
while (currCharOfs < end) {
if (sql[currCharOfs++] == '"') {
ch = '\0';
goto None;
}
}
goto Finish;
EscapedStart:
if (currCharOfs < end) {
lastChar = ch;
ch = sql[currCharOfs++];
if (ch == '\'') {
goto Escaped;
}
goto NoneContinue;
}
goto Finish;
Escaped:
while (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '\'') {
goto MaybeConcatenatedEscaped;
}
if (ch == '\\') {
if (currCharOfs >= end) {
goto Finish;
}
currCharOfs++;
}
}
goto Finish;
MaybeConcatenatedEscaped:
while (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n') {
goto MaybeConcatenatedEscaped2;
}
if (ch != ' ' && ch != '\t' && ch != '\f') {
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscaped2:
while (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '\'') {
goto Escaped;
}
if (ch == '-') {
if (currCharOfs >= end) {
goto Finish;
}
ch = sql[currCharOfs++];
if (ch == '-') {
goto MaybeConcatenatedEscapeAfterComment;
}
lastChar = '\0';
goto NoneContinue;
}
if (ch != ' ' && ch != '\t' && ch != '\n' & ch != '\r' && ch != '\f') {
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscapeAfterComment:
while (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n') {
goto MaybeConcatenatedEscaped2;
}
}
goto Finish;
DollarQuotedStart:
if (currCharOfs < end) {
ch = sql[currCharOfs];
if (ch == '$') {
// Empty tag
dollarTagStart = dollarTagEnd = currCharOfs;
currCharOfs++;
goto DollarQuoted;
}
if (IsIdentifierStart(ch)) {
dollarTagStart = currCharOfs;
currCharOfs++;
goto DollarQuotedInFirstDelim;
}
lastChar = '$';
currCharOfs++;
goto NoneContinue;
}
goto Finish;
DollarQuotedInFirstDelim:
while (currCharOfs < end) {
lastChar = ch;
ch = sql[currCharOfs++];
if (ch == '$') {
dollarTagEnd = currCharOfs - 1;
goto DollarQuoted;
}
if (!IsDollarTagIdentifier(ch)) {
goto NoneContinue;
}
}
goto Finish;
DollarQuoted: {
var tag = sql.Substring(dollarTagStart - 1, dollarTagEnd - dollarTagStart + 2);
var pos = sql.IndexOf(tag, dollarTagEnd + 1); // Not linear time complexity, but that's probably not a problem, since PostgreSQL backend's isn't either
if (pos == -1) {
currCharOfs = end;
goto Finish;
}
currCharOfs = pos + dollarTagEnd - dollarTagStart + 2;
ch = '\0';
goto None;
}
LineCommentBegin:
if (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '-') {
goto LineComment;
}
lastChar = '\0';
goto NoneContinue;
}
goto Finish;
LineComment:
while (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n') {
goto None;
}
}
goto Finish;
BlockCommentBegin:
while (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '*') {
blockCommentLevel++;
goto BlockComment;
}
if (ch != '/') {
if (blockCommentLevel > 0) {
goto BlockComment;
}
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
BlockComment:
while (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '*') {
goto BlockCommentEnd;
}
if (ch == '/') {
goto BlockCommentBegin;
}
}
goto Finish;
BlockCommentEnd:
while (currCharOfs < end) {
ch = sql[currCharOfs++];
if (ch == '/') {
if (--blockCommentLevel > 0) {
goto BlockComment;
}
goto None;
}
if (ch != '*') {
goto BlockComment;
}
}
goto Finish;
SemiColon:
currentSql.Write(sql.Substring(currTokenBeg, currCharOfs - currTokenBeg - 1));
queries.Add(new QueryDetails(currentSql.ToString(), currentParameters));
while (currCharOfs < end) {
ch = sql[currCharOfs];
if (Char.IsWhiteSpace(ch)) {
currCharOfs++;
continue;
}
// TODO: Handle end of line comment? Although psql doesn't seem to handle them...
currTokenBeg = currCharOfs;
paramIndexMap.Clear();
if (queries.Count > NpgsqlCommand.MaxQueriesInMultiquery) {
throw new NotSupportedException(String.Format("A single command cannot contain more than {0} queries", NpgsqlCommand.MaxQueriesInMultiquery));
}
currentSql = new StringWriter();
currentParameters = new List<NpgsqlParameter>();
goto None;
}
return;
Finish:
currentSql.Write(sql.Substring(currTokenBeg, end - currTokenBeg));
queries.Add(new QueryDetails(currentSql.ToString(), currentParameters));
}
/// <summary>
/// Receives a raw SQL query as passed in by the user, and performs some processing necessary
/// before sending to the backend.
/// This includes doing parameter placebolder processing (@p => $1), and splitting the query
/// up by semicolons if needed (SELECT 1; SELECT 2)
/// </summary>
/// <param name="sql">Raw user-provided query.</param>
/// <param name="standardConformantStrings">Whether the PostgreSQL session is configured to use standard conformant strings.</param>
/// <param name="parameters">The parameters configured on the <see cref="NpgsqlCommand"/> of this query.</param>
/// <param name="queries">An empty list to be populated with the queries parsed by this method</param>
static internal void ParseRawQuery(string sql, bool standardConformantStrings, NpgsqlParameterCollection parameters, List <QueryDetails> queries)
{
Contract.Requires(sql != null);
Contract.Requires(queries != null && !queries.Any());
var currCharOfs = 0;
var end = sql.Length;
var ch = '\0';
var lastChar = '\0';
var dollarTagStart = 0;
var dollarTagEnd = 0;
var currTokenBeg = 0;
var blockCommentLevel = 0;
queries.Clear();
// TODO: Recycle
var paramIndexMap = new Dictionary <string, int>();
var currentSql = new StringWriter();
var currentParameters = new List <NpgsqlParameter>();
None:
if (currCharOfs >= end)
{
goto Finish;
}
lastChar = ch;
ch = sql[currCharOfs++];
NoneContinue:
for (; ; lastChar = ch, ch = sql[currCharOfs++])
{
switch (ch)
{
case '/':
goto BlockCommentBegin;
case '-':
goto LineCommentBegin;
case '\'':
if (standardConformantStrings)
{
goto Quoted;
}
else
{
goto Escaped;
}
case '$':
if (!IsIdentifier(lastChar))
{
goto DollarQuotedStart;
}
else
{
break;
}
case '"':
goto DoubleQuoted;
case ':':
if (lastChar != ':')
{
goto ParamStart;
}
else
{
break;
}
case '@':
if (lastChar != '@')
{
goto ParamStart;
}
else
{
break;
}
case ';':
goto SemiColon;
case 'e':
case 'E':
if (!IsLetter(lastChar))
{
goto EscapedStart;
}
else
{
break;
}
}
if (currCharOfs >= end)
{
goto Finish;
}
}
ParamStart:
if (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs];
if (IsParamNameChar(ch))
{
if (currCharOfs - 1 > currTokenBeg)
{
currentSql.Write(sql.Substring(currTokenBeg, currCharOfs - 1 - currTokenBeg));
}
currTokenBeg = currCharOfs++ - 1;
goto Param;
}
else
{
currCharOfs++;
goto NoneContinue;
}
}
goto Finish;
Param:
// We have already at least one character of the param name
for (; ;)
{
lastChar = ch;
if (currCharOfs >= end || !IsParamNameChar(ch = sql[currCharOfs]))
{
var paramName = sql.Substring(currTokenBeg, currCharOfs - currTokenBeg);
int index;
if (!paramIndexMap.TryGetValue(paramName, out index))
{
// Parameter hasn't been seen before in this query
NpgsqlParameter parameter;
if (!parameters.TryGetValue(paramName, out parameter))
{
throw new Exception(String.Format("Parameter '{0}' referenced in SQL but not found in parameter list", paramName));
}
if (!parameter.IsInputDirection)
{
throw new Exception(String.Format("Parameter '{0}' referenced in SQL but is an out-only parameter", paramName));
}
currentParameters.Add(parameter);
index = paramIndexMap[paramName] = currentParameters.Count;
}
currentSql.Write('$');
currentSql.Write(index);
currTokenBeg = currCharOfs;
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
goto NoneContinue;
}
else
{
currCharOfs++;
}
}
Quoted:
while (currCharOfs < end)
{
if (sql[currCharOfs++] == '\'')
{
ch = '\0';
goto None;
}
}
goto Finish;
DoubleQuoted:
while (currCharOfs < end)
{
if (sql[currCharOfs++] == '"')
{
ch = '\0';
goto None;
}
}
goto Finish;
EscapedStart:
if (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs++];
if (ch == '\'')
{
goto Escaped;
}
goto NoneContinue;
}
goto Finish;
Escaped:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\'')
{
goto MaybeConcatenatedEscaped;
}
if (ch == '\\')
{
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
}
}
goto Finish;
MaybeConcatenatedEscaped:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto MaybeConcatenatedEscaped2;
}
if (ch != ' ' && ch != '\t' && ch != '\f')
{
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscaped2:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\'')
{
goto Escaped;
}
if (ch == '-')
{
if (currCharOfs >= end)
{
goto Finish;
}
ch = sql[currCharOfs++];
if (ch == '-')
{
goto MaybeConcatenatedEscapeAfterComment;
}
lastChar = '\0';
goto NoneContinue;
}
if (ch != ' ' && ch != '\t' && ch != '\n' & ch != '\r' && ch != '\f')
{
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscapeAfterComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto MaybeConcatenatedEscaped2;
}
}
goto Finish;
DollarQuotedStart:
if (currCharOfs < end)
{
ch = sql[currCharOfs];
if (ch == '$')
{
// Empty tag
dollarTagStart = dollarTagEnd = currCharOfs;
currCharOfs++;
goto DollarQuoted;
}
if (IsIdentifierStart(ch))
{
dollarTagStart = currCharOfs;
currCharOfs++;
goto DollarQuotedInFirstDelim;
}
lastChar = '$';
currCharOfs++;
goto NoneContinue;
}
goto Finish;
DollarQuotedInFirstDelim:
while (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs++];
if (ch == '$')
{
dollarTagEnd = currCharOfs - 1;
goto DollarQuoted;
}
if (!IsDollarTagIdentifier(ch))
{
goto NoneContinue;
}
}
goto Finish;
DollarQuoted : {
var tag = sql.Substring(dollarTagStart - 1, dollarTagEnd - dollarTagStart + 2);
var pos = sql.IndexOf(tag, dollarTagEnd + 1); // Not linear time complexity, but that's probably not a problem, since PostgreSQL backend's isn't either
if (pos == -1)
{
currCharOfs = end;
goto Finish;
}
currCharOfs = pos + dollarTagEnd - dollarTagStart + 2;
ch = '\0';
goto None;
}
LineCommentBegin:
if (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '-')
{
goto LineComment;
}
lastChar = '\0';
goto NoneContinue;
}
goto Finish;
LineComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto None;
}
}
goto Finish;
BlockCommentBegin:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '*')
{
blockCommentLevel++;
goto BlockComment;
}
if (ch != '/')
{
if (blockCommentLevel > 0)
{
goto BlockComment;
}
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
BlockComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '*')
{
goto BlockCommentEnd;
}
if (ch == '/')
{
goto BlockCommentBegin;
}
}
goto Finish;
BlockCommentEnd:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '/')
{
if (--blockCommentLevel > 0)
{
goto BlockComment;
}
goto None;
}
if (ch != '*')
{
goto BlockComment;
}
}
goto Finish;
SemiColon:
currentSql.Write(sql.Substring(currTokenBeg, currCharOfs - currTokenBeg - 1));
queries.Add(new QueryDetails(currentSql.ToString(), currentParameters));
while (currCharOfs < end)
{
ch = sql[currCharOfs];
if (Char.IsWhiteSpace(ch))
{
currCharOfs++;
continue;
}
// TODO: Handle end of line comment? Although psql doesn't seem to handle them...
currTokenBeg = currCharOfs;
paramIndexMap.Clear();
if (queries.Count > NpgsqlCommand.MaxQueriesInMultiquery)
{
throw new NotSupportedException(String.Format("A single command cannot contain more than {0} queries", NpgsqlCommand.MaxQueriesInMultiquery));
}
currentSql = new StringWriter();
currentParameters = new List <NpgsqlParameter>();
goto None;
}
return;
Finish:
currentSql.Write(sql.Substring(currTokenBeg, end - currTokenBeg));
queries.Add(new QueryDetails(currentSql.ToString(), currentParameters));
}
/// <summary>
/// Initializes a new instance of the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> class with the text of the query, a <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see>, and the <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see>.
/// </summary>
/// <param name="cmdText">The text of the query.</param>
/// <param name="connection">A <see cref="Npgsql.NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
/// <param name="transaction">The <see cref="Npgsql.NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="Npgsql.NpgsqlCommand">NpgsqlCommand</see> executes.</param>
public NpgsqlCommand(String cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction)
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
planName = String.Empty;
text = cmdText;
this.connection = connection;
if (this.connection != null)
this.connector = connection.Connector;
parameters = new NpgsqlParameterCollection();
timeout = 20;
type = CommandType.Text;
this.Transaction = transaction;
commandBehavior = CommandBehavior.Default;
}
/// <summary>
/// Used to execute internal commands.
/// </summary>
internal NpgsqlCommand(String cmdText, NpgsqlConnector connector)
{
resman = new System.Resources.ResourceManager(this.GetType());
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME);
planName = String.Empty;
text = cmdText;
this.connector = connector;
type = CommandType.Text;
commandBehavior = CommandBehavior.Default;
parameters = new NpgsqlParameterCollection();
timeout = 20;
}
/// <summary>
/// Receives a raw SQL query as passed in by the user, and performs some processing necessary
/// before sending to the backend.
/// This includes doing parameter placebolder processing (@p => $1), and splitting the query
/// up by semicolons if needed (SELECT 1; SELECT 2)
/// </summary>
/// <param name="sql">Raw user-provided query.</param>
/// <param name="standardConformantStrings">Whether the PostgreSQL session is configured to use standard conformant strings.</param>
/// <param name="parameters">The parameters configured on the <see cref="NpgsqlCommand"/> of this query.</param>
/// <param name="statements">An empty list to be populated with the statements parsed by this method</param>
internal void ParseRawQuery(string sql, bool standardConformantStrings, NpgsqlParameterCollection parameters, List <NpgsqlStatement> statements)
{
Debug.Assert(sql != null);
Debug.Assert(statements != null);
_statements = statements;
_statementIndex = -1;
MoveToNextStatement();
var currCharOfs = 0;
var end = sql.Length;
var ch = '\0';
int dollarTagStart;
int dollarTagEnd;
var currTokenBeg = 0;
var blockCommentLevel = 0;
var parenthesisLevel = 0;
None:
if (currCharOfs >= end)
{
goto Finish;
}
var lastChar = ch;
ch = sql[currCharOfs++];
NoneContinue:
for (; ; lastChar = ch, ch = sql[currCharOfs++])
{
switch (ch)
{
case '/':
goto BlockCommentBegin;
case '-':
goto LineCommentBegin;
case '\'':
if (standardConformantStrings)
{
goto Quoted;
}
else
{
goto Escaped;
}
case '$':
if (!IsIdentifier(lastChar))
{
goto DollarQuotedStart;
}
else
{
break;
}
case '"':
goto DoubleQuoted;
case ':':
if (lastChar != ':')
{
goto ParamStart;
}
else
{
break;
}
case '@':
if (lastChar != '@')
{
goto ParamStart;
}
else
{
break;
}
case ';':
if (parenthesisLevel == 0)
{
goto SemiColon;
}
break;
case '(':
parenthesisLevel++;
break;
case ')':
parenthesisLevel--;
break;
case 'e':
case 'E':
if (!IsLetter(lastChar))
{
goto EscapedStart;
}
else
{
break;
}
}
if (currCharOfs >= end)
{
goto Finish;
}
}
ParamStart:
if (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs];
if (IsParamNameChar(ch))
{
if (currCharOfs - 1 > currTokenBeg)
{
_rewrittenSql.Append(sql.Substring(currTokenBeg, currCharOfs - 1 - currTokenBeg));
}
currTokenBeg = currCharOfs++ - 1;
goto Param;
}
else
{
currCharOfs++;
goto NoneContinue;
}
}
goto Finish;
Param:
// We have already at least one character of the param name
for (;;)
{
lastChar = ch;
if (currCharOfs >= end || !IsParamNameChar(ch = sql[currCharOfs]))
{
var paramName = sql.Substring(currTokenBeg, currCharOfs - currTokenBeg);
int index;
if (!_paramIndexMap.TryGetValue(paramName, out index))
{
// Parameter hasn't been seen before in this query
NpgsqlParameter parameter;
if (!parameters.TryGetValue(paramName, out parameter))
{
_rewrittenSql.Append(paramName);
currTokenBeg = currCharOfs;
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
goto NoneContinue;
}
if (!parameter.IsInputDirection)
{
throw new Exception($"Parameter '{paramName}' referenced in SQL but is an out-only parameter");
}
_statement.InputParameters.Add(parameter);
index = _paramIndexMap[paramName] = _statement.InputParameters.Count;
}
_rewrittenSql.Append('$');
_rewrittenSql.Append(index);
currTokenBeg = currCharOfs;
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
goto NoneContinue;
}
else
{
currCharOfs++;
}
}
Quoted:
while (currCharOfs < end)
{
if (sql[currCharOfs++] == '\'')
{
ch = '\0';
goto None;
}
}
goto Finish;
DoubleQuoted:
while (currCharOfs < end)
{
if (sql[currCharOfs++] == '"')
{
ch = '\0';
goto None;
}
}
goto Finish;
EscapedStart:
if (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs++];
if (ch == '\'')
{
goto Escaped;
}
goto NoneContinue;
}
goto Finish;
Escaped:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\'')
{
goto MaybeConcatenatedEscaped;
}
if (ch == '\\')
{
if (currCharOfs >= end)
{
goto Finish;
}
currCharOfs++;
}
}
goto Finish;
MaybeConcatenatedEscaped:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto MaybeConcatenatedEscaped2;
}
if (ch != ' ' && ch != '\t' && ch != '\f')
{
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscaped2:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\'')
{
goto Escaped;
}
if (ch == '-')
{
if (currCharOfs >= end)
{
goto Finish;
}
ch = sql[currCharOfs++];
if (ch == '-')
{
goto MaybeConcatenatedEscapeAfterComment;
}
lastChar = '\0';
goto NoneContinue;
}
if (ch != ' ' && ch != '\t' && ch != '\n' & ch != '\r' && ch != '\f')
{
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
MaybeConcatenatedEscapeAfterComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto MaybeConcatenatedEscaped2;
}
}
goto Finish;
DollarQuotedStart:
if (currCharOfs < end)
{
ch = sql[currCharOfs];
if (ch == '$')
{
// Empty tag
dollarTagStart = dollarTagEnd = currCharOfs;
currCharOfs++;
goto DollarQuoted;
}
if (IsIdentifierStart(ch))
{
dollarTagStart = currCharOfs;
currCharOfs++;
goto DollarQuotedInFirstDelim;
}
lastChar = '$';
currCharOfs++;
goto NoneContinue;
}
goto Finish;
DollarQuotedInFirstDelim:
while (currCharOfs < end)
{
lastChar = ch;
ch = sql[currCharOfs++];
if (ch == '$')
{
dollarTagEnd = currCharOfs - 1;
goto DollarQuoted;
}
if (!IsDollarTagIdentifier(ch))
{
goto NoneContinue;
}
}
goto Finish;
DollarQuoted : {
var tag = sql.Substring(dollarTagStart - 1, dollarTagEnd - dollarTagStart + 2);
var pos = sql.IndexOf(tag, dollarTagEnd + 1); // Not linear time complexity, but that's probably not a problem, since PostgreSQL backend's isn't either
if (pos == -1)
{
currCharOfs = end;
goto Finish;
}
currCharOfs = pos + dollarTagEnd - dollarTagStart + 2;
ch = '\0';
goto None;
}
LineCommentBegin:
if (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '-')
{
goto LineComment;
}
lastChar = '\0';
goto NoneContinue;
}
goto Finish;
LineComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '\r' || ch == '\n')
{
goto None;
}
}
goto Finish;
BlockCommentBegin:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '*')
{
blockCommentLevel++;
goto BlockComment;
}
if (ch != '/')
{
if (blockCommentLevel > 0)
{
goto BlockComment;
}
lastChar = '\0';
goto NoneContinue;
}
}
goto Finish;
BlockComment:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '*')
{
goto BlockCommentEnd;
}
if (ch == '/')
{
goto BlockCommentBegin;
}
}
goto Finish;
BlockCommentEnd:
while (currCharOfs < end)
{
ch = sql[currCharOfs++];
if (ch == '/')
{
if (--blockCommentLevel > 0)
{
goto BlockComment;
}
goto None;
}
if (ch != '*')
{
goto BlockComment;
}
}
goto Finish;
SemiColon:
_rewrittenSql.Append(sql.Substring(currTokenBeg, currCharOfs - currTokenBeg - 1));
_statement.SQL = _rewrittenSql.ToString();
while (currCharOfs < end)
{
ch = sql[currCharOfs];
if (char.IsWhiteSpace(ch))
{
currCharOfs++;
continue;
}
// TODO: Handle end of line comment? Although psql doesn't seem to handle them...
currTokenBeg = currCharOfs;
if (_rewrittenSql.Length > 0)
{
MoveToNextStatement();
}
goto None;
}
return;
Finish:
_rewrittenSql.Append(sql.Substring(currTokenBeg, end - currTokenBeg));
_statement.SQL = _rewrittenSql.ToString();
if (statements.Count > _statementIndex + 1)
{
statements.RemoveRange(_statementIndex + 1, statements.Count - (_statementIndex + 1));
}
}
internal void SendBind(string portalName, string statementName, NpgsqlParameterCollection parameters, short[] resultFormatCodes)
{
_log.Debug("Sending bind message");
var portalNameBytes = BackendEncoding.UTF8Encoding.GetBytes(portalName);
var statementNameBytes = BackendEncoding.UTF8Encoding.GetBytes(statementName);
// When sending format codes, the list can be optimized if all format codes are the same.
// If all are text, we send an empty list.
// If all are binary, we send a list with length one, containing a 1 (indicates binary).
// Otherwise, we send the whole list.
var len = 0;
var numInputParameters = 0;
var allSameFormatCode = -1;
for (var i = 0; i < parameters.Count; i++)
{
if (parameters[i].IsInputDirection)
{
numInputParameters++;
len += 4;
if (parameters[i].BoundValue != null)
{
len += parameters[i].BoundValue.Length;
}
if (allSameFormatCode == -1)
{
allSameFormatCode = parameters[i].BoundFormatCode;
}
else if (allSameFormatCode != parameters[i].BoundFormatCode)
{
allSameFormatCode = -2;
}
}
}
len +=
4 + // Message length (32 bits)
portalNameBytes.Length + 1 + // Portal name + null terminator
statementNameBytes.Length + 1 + // Statement name + null terminator
2 + // Parameter format code array length (16 bits)
(allSameFormatCode >= 0 ? allSameFormatCode : numInputParameters) * 2 + // Parameter format code array (16 bits per code)
2; // Parameter value array length (16 bits)
var allSameResultFormatCode = -1;
for (var i = 0; i < resultFormatCodes.Length; i++)
{
if (allSameResultFormatCode == -1)
{
allSameResultFormatCode = resultFormatCodes[i];
}
else if (allSameResultFormatCode != resultFormatCodes[i])
{
allSameResultFormatCode = -2;
}
}
len +=
2 + // Result format code array length (16 bits)
(allSameResultFormatCode >= 0 ? allSameResultFormatCode : resultFormatCodes.Length) * 2; // Result format code array (16 bits per code)
Stream
.WriteByte(ASCIIByteArrays.BindMessageCode)
.WriteInt32(len)
.WriteBytesNullTerminated(portalNameBytes)
.WriteBytesNullTerminated(statementNameBytes)
.WriteInt16((short)(allSameFormatCode >= 0 ? allSameFormatCode : numInputParameters));
if (parameters.Count > 0)
{
if (allSameFormatCode == 1)
{
Stream.WriteInt16((short)FormatCode.Binary);
}
else if (allSameFormatCode == -2)
{
for (var i = 0; i < parameters.Count; i++)
{
if (parameters[i].IsInputDirection)
{
Stream.WriteInt16(parameters[i].BoundFormatCode);
}
}
}
Stream.WriteInt16((short)numInputParameters);
for (var i = 0; i < parameters.Count; i++)
{
if (parameters[i].IsInputDirection)
{
var value = parameters[i].BoundValue;
if (value == null)
{
Stream.WriteInt32(-1);
}
else
{
Stream
.WriteInt32(value.Length)
.WriteBytes(value);
}
}
}
}
else
{
Stream.WriteInt16(0); // Number of parameter values sent
}
Stream.WriteInt16((short)(allSameResultFormatCode >= 0 ? allSameResultFormatCode : resultFormatCodes.Length));
if (allSameResultFormatCode == 1)
{
Stream.WriteInt16((short)FormatCode.Binary);
}
else if (allSameResultFormatCode == -2)
{
foreach (var code in resultFormatCodes)
{
Stream.WriteInt16(code);
}
}
}