/// <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); } } }