public SnowflakeDbCommand()
{
logger.Debug("Constucting SnowflakeDbCommand class");
// by default, no query timeout
this.CommandTimeout = 0;
parameterCollection = new SnowflakeDbParameterCollection();
}
internal void Open()
{
logger.Debug("Open Session");
if (authenticator == null)
{
authenticator = AuthenticatorFactory.GetAuthenticator(this);
}
authenticator.Authenticate();
}
public async Task <T> PostAsync <T>(IRestRequest request, CancellationToken cancellationToken)
{
var req = request.ToRequestMessage(HttpMethod.Post);
var response = await SendAsync(req, request.GetRestTimeout(), cancellationToken).ConfigureAwait(false);
var json = await response.Content.ReadAsStringAsync().ConfigureAwait(false);
logger.Debug($"Post response: {json}");
return(JsonConvert.DeserializeObject <T>(json));
}
public async Task <T> PostAsync <T>(SFRestRequest postRequest, CancellationToken cancellationToken)
{
var req = ToRequestMessage(HttpMethod.Post, postRequest);
var response = await SendAsync(req, postRequest.sfRestRequestTimeout, cancellationToken);
var json = await response.Content.ReadAsStringAsync();
logger.Debug($"Post response: {json}");
return(JsonConvert.DeserializeObject <T>(json));
}
public override void ChangeDatabase(string databaseName)
{
logger.Debug($"ChangeDatabase to:{databaseName}");
string alterDbCommand = $"use database {databaseName}";
using (IDbCommand cmd = this.CreateCommand())
{
cmd.CommandText = alterDbCommand;
cmd.ExecuteNonQuery();
}
}
internal void Open()
{
logger.Debug("Open Session");
var loginRequest = BuildLoginRequest();
if (logger.IsDebugEnabled())
{
logger.Debug($"Login Request Data: {loginRequest.ToString()}");
}
var response = restRequest.Post <AuthnResponse>(loginRequest);
ProcessLoginResponse(response);
}
/// <see cref="IAuthenticator"/>
async Task IAuthenticator.AuthenticateAsync(CancellationToken cancellationToken)
{
logger.Info("External Browser Authentication");
int localPort = GetRandomUnusedPort();
string proofKey;
string samlResponseToken;
using (var httpListener = GetHttpListener(localPort))
{
httpListener.Start();
logger.Debug("Get IdpUrl and ProofKey");
var authenticatorRestRequest = BuildAuthenticatorRestRequest(localPort);
var authenticatorRestResponse =
await session.restRequester.PostAsync <AuthenticatorResponse>(
authenticatorRestRequest,
cancellationToken
).ConfigureAwait(false);
authenticatorRestResponse.FilterFailedResponse();
var idpUrl = authenticatorRestResponse.data.ssoUrl;
proofKey = authenticatorRestResponse.data.proofKey;
logger.Debug("Open browser");
StartBrowser(idpUrl);
logger.Debug("Get the redirect SAML request");
var context = await httpListener.GetContextAsync().ConfigureAwait(false);
var request = context.Request;
samlResponseToken = ValidateAndExtractToken(request);
HttpListenerResponse response = context.Response;
try
{
using (var output = response.OutputStream)
{
await output.WriteAsync(SUCCESS_RESPONSE, 0, SUCCESS_RESPONSE.Length).ConfigureAwait(false);
}
}
catch (Exception e)
{
// Ignore the exception as it does not affect the overall authentication flow
logger.Warn("External browser response not sent out");
}
httpListener.Stop();
}
logger.Debug("Send login request");
var loginResponse = await session.restRequester.PostAsync <LoginResponse>(
BuildExternalBrowserLoginRequest(samlResponseToken, proofKey),
cancellationToken
).ConfigureAwait(false);
session.ProcessLoginResponse(loginResponse);
}
public void TestCancelLoginBeforeTimeout()
{
using (var conn = new MockSnowflakeDbConnection())
{
// No timeout
int timeoutSec = 0;
string infiniteLoginTimeOut = String.Format(ConnectionString + ";connection_timeout={0}",
timeoutSec);
conn.ConnectionString = infiniteLoginTimeOut;
Assert.AreEqual(conn.State, ConnectionState.Closed);
// At this point the connection string has not been parsed, it will return the
// default value
//Assert.AreEqual(15, conn.ConnectionTimeout);
CancellationTokenSource connectionCancelToken = new CancellationTokenSource();
logger.Debug("TestNoLoginTimeout token " + connectionCancelToken.Token.GetHashCode());
Task connectTask = conn.OpenAsync(connectionCancelToken.Token);
// Sleep for 16min (more than the default connection timeout and the httpclient
// timeout to make sure there are no false positive )
Thread.Sleep(16 * 60 * 1000);
Assert.AreEqual(ConnectionState.Connecting, conn.State);
// Cancel the connection because it will never succeed since there is no
// connection_timeout defined
logger.Debug("connectionCancelToken.Cancel ");
connectionCancelToken.Cancel();
try
{
connectTask.Wait();
}
catch (AggregateException e)
{
Assert.AreEqual(
"System.Threading.Tasks.TaskCanceledException",
e.InnerException.GetType().ToString());
}
Assert.AreEqual(ConnectionState.Closed, conn.State);
Assert.AreEqual(0, conn.ConnectionTimeout);
}
}
public SnowflakeDbTransaction(IsolationLevel isolationLevel, SnowflakeDbConnection connection)
{
logger.Debug("Begin transaction.");
if (isolationLevel != IsolationLevel.ReadCommitted)
{
throw new SnowflakeDbException(SFError.UNSUPPORTED_FEATURE);
}
this.isolationLevel = isolationLevel;
this.connection = connection;
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = "BEGIN";
command.ExecuteNonQuery();
}
}
public override void Open()
{
logger.Debug("Open Connection.");
SetMockSession();
try
{
SfSession.Open();
}
catch (Exception e)
{
// Otherwise when Dispose() is called, the close request would timeout.
_connectionState = System.Data.ConnectionState.Closed;
logger.Error("Unable to connect", e);
throw;
}
OnSessionEstablished();
}
internal void resetChunkInfo(SFResultChunk nextChunk)
{
Logger.Debug($"Recieved chunk #{nextChunk.ChunkIndex + 1} of {_totalChunkCount}");
_currentChunk.rowSet = null;
_currentChunk = nextChunk;
_currentChunkRowIdx = 0;
_currentChunkRowCount = _currentChunk.rowCount;
}
public SnowflakeDbCommand(SnowflakeDbConnection connection)
{
logger.Debug("Constucting SnowflakeDbCommand class");
this.connection = connection;
this.sfStatement = new SFStatement(connection.SfSession);
// by default, no query timeout
this.CommandTimeout = 0;
parameterCollection = new SnowflakeDbParameterCollection();
}
internal void resetChunkInfo(IResultChunk nextChunk)
{
Logger.Debug($"Recieved chunk #{nextChunk.GetChunkIndex() + 1} of {_totalChunkCount}");
if (_currentChunk is SFResultChunk)
{
((SFResultChunk)_currentChunk).rowSet = null;
}
_currentChunk = nextChunk;
_currentChunkRowIdx = 0;
_currentChunkRowCount = _currentChunk.GetRowCount();
}
internal static object ConvertToCSharpVal(string srcVal, SFDataType srcType, Type destType)
{
Logger.Debug($"src value: {srcVal}, srcType: {srcType}, destType: {destType}");
if (srcVal == null)
{
return(DBNull.Value);
}
if (destType == typeof(short) ||
destType == typeof(int) ||
destType == typeof(long) ||
destType == typeof(Guid) ||
destType == typeof(double) ||
destType == typeof(float) ||
destType == typeof(decimal))
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
var typeConverter = TypeDescriptor.GetConverter(destType);
return(typeConverter.ConvertFrom(srcVal));
}
else if (destType == typeof(Boolean))
{
return(srcVal.Equals("1", StringComparison.OrdinalIgnoreCase) ||
srcVal.Equals("true", StringComparison.OrdinalIgnoreCase));
}
else if (destType == typeof(string))
{
return(srcVal);
}
else if (destType == typeof(DateTime))
{
return(ConvertToDateTime(srcVal, srcType));
}
else if (destType == typeof(DateTimeOffset))
{
return(ConvertToDateTimeOffset(srcVal, srcType));
}
else if (destType == typeof(byte[]))
{
return(srcType == SFDataType.BINARY ?
HexToBytes(srcVal) : Encoding.UTF8.GetBytes(srcVal));
}
else
{
throw new SnowflakeDbException(SFError.INTERNAL_ERROR, "Invalid destination type.");
}
}
private static void checkSessionProperties(SFSessionProperties properties)
{
foreach (SFSessionProperty sessionProperty in Enum.GetValues(typeof(SFSessionProperty)))
{
// if required property, check if exists in the dictionary
if (IsRequired(sessionProperty, properties) &&
!properties.ContainsKey(sessionProperty))
{
SnowflakeDbException e = new SnowflakeDbException(SFError.MISSING_CONNECTION_PROPERTY,
sessionProperty);
logger.Error("Missing connetion property", e);
throw e;
}
// add default value to the map
string defaultVal = sessionProperty.GetAttribute <SFSessionPropertyAttr>().defaultValue;
if (defaultVal != null && !properties.ContainsKey(sessionProperty))
{
logger.Debug($"Sesssion property {sessionProperty} set to default value: {defaultVal}");
properties.Add(sessionProperty, defaultVal);
}
}
}
protected override async Task <HttpResponseMessage> SendAsync(HttpRequestMessage requestMessage,
CancellationToken cancellationToken)
{
HttpResponseMessage response = null;
int backOffInSec = 1;
TimeSpan httpTimeout = (TimeSpan)requestMessage.Properties["TIMEOUT_PER_HTTP_REQUEST"];
CancellationTokenSource childCts = null;
while (true)
{
try
{
childCts = null;
if (!httpTimeout.Equals(Timeout.InfiniteTimeSpan))
{
childCts = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken);
childCts.CancelAfter(httpTimeout);
}
response = await base.SendAsync(requestMessage, childCts == null?
cancellationToken : childCts.Token);
}
catch (Exception e)
{
if (cancellationToken.IsCancellationRequested)
{
logger.Debug("SF rest request timeout.");
cancellationToken.ThrowIfCancellationRequested();
}
else if (childCts != null && childCts.Token.IsCancellationRequested)
{
logger.Warn("Http request timeout. Retry the request");
}
else
{
//TODO: Should probably check to see if the error is recoverable or transient.
logger.Warn("Error occurred during request, retrying...", e);
}
}
if (response != null)
{
if (response.IsSuccessStatusCode)
{
logger.Debug($"Success Response: {response.ToString()}");
return(response);
}
logger.Debug($"Failed Response: {response.ToString()}");
}
else
{
logger.Info("Response returned was null.");
}
logger.Debug($"Sleep {backOffInSec} seconds and then retry the request");
Thread.Sleep(backOffInSec * 1000);
backOffInSec = backOffInSec >= 16 ? 16 : backOffInSec * 2;
}
}
protected override async Task <HttpResponseMessage> SendAsync(HttpRequestMessage requestMessage,
CancellationToken cancellationToken)
{
HttpResponseMessage response = null;
int backOffInSec = 1;
int totalRetryTime = 0;
int maxDefaultBackoff = 16;
ServicePoint p = ServicePointManager.FindServicePoint(requestMessage.RequestUri);
p.Expect100Continue = false; // Saves about 100 ms per request
p.UseNagleAlgorithm = false; // Saves about 200 ms per request
p.ConnectionLimit = 20; // Default value is 2, we need more connections for performing multiple parallel queries
TimeSpan httpTimeout = (TimeSpan)requestMessage.Properties[SFRestRequest.HTTP_REQUEST_TIMEOUT_KEY];
TimeSpan restTimeout = (TimeSpan)requestMessage.Properties[SFRestRequest.REST_REQUEST_TIMEOUT_KEY];
if (logger.IsDebugEnabled())
{
logger.Debug("Http request timeout : " + httpTimeout);
logger.Debug("Rest request timeout : " + restTimeout);
}
CancellationTokenSource childCts = null;
UriUpdater updater = new UriUpdater(requestMessage.RequestUri);
while (true)
{
try
{
childCts = null;
if (!httpTimeout.Equals(Timeout.InfiniteTimeSpan))
{
childCts = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken);
childCts.CancelAfter(httpTimeout);
}
response = await base.SendAsync(requestMessage, childCts == null?
cancellationToken : childCts.Token).ConfigureAwait(false);
}
catch (Exception e)
{
if (cancellationToken.IsCancellationRequested)
{
logger.Debug("SF rest request timeout or explicit cancel called.");
cancellationToken.ThrowIfCancellationRequested();
}
else if (childCts != null && childCts.Token.IsCancellationRequested)
{
logger.Warn("Http request timeout. Retry the request");
totalRetryTime += (int)httpTimeout.TotalSeconds;
}
else
{
//TODO: Should probably check to see if the error is recoverable or transient.
logger.Warn("Error occurred during request, retrying...", e);
}
}
if (response != null)
{
if (response.IsSuccessStatusCode)
{
return(response);
}
else
{
logger.Debug($"Failed Response: {response.ToString()}");
bool isRetryable = isRetryableHTTPCode((int)response.StatusCode);
if (!isRetryable)
{
// No need to keep retrying, stop here
return(response);
}
}
}
else
{
logger.Info("Response returned was null.");
}
requestMessage.RequestUri = updater.Update();
logger.Debug($"Sleep {backOffInSec} seconds and then retry the request");
Thread.Sleep(backOffInSec * 1000);
totalRetryTime += backOffInSec;
backOffInSec = backOffInSec >= maxDefaultBackoff ?
maxDefaultBackoff : backOffInSec * 2;
if (totalRetryTime + backOffInSec > restTimeout.TotalSeconds)
{
// No need to wait more than necessary if it can be avoided.
// If the rest timeout will be reached before the next back-off,
// use a smaller one to give the Rest request a chance to timeout early
backOffInSec = Math.Max(1, (int)restTimeout.TotalSeconds - totalRetryTime - 1);
}
}
}
/// <see cref="IAuthenticator"/>
async Task IAuthenticator.AuthenticateAsync(CancellationToken cancellationToken)
{
logger.Info("Okta Authentication");
// Clear cookies before authenticating because when a cookie is present in the request,
// Okta will assume it is coming from a browser and perform a CSRF check.
// This will ensure that we are NOT including the ‘sid’ cookie with the request.
HttpUtil.ClearCookies(oktaUrl);
logger.Debug("step 1: get sso and token url");
var authenticatorRestRequest = BuildAuthenticatorRestRequest();
var authenticatorResponse = await session.restRequester.PostAsync <AuthenticatorResponse>(authenticatorRestRequest, cancellationToken).ConfigureAwait(false);
authenticatorResponse.FilterFailedResponse();
Uri ssoUrl = new Uri(authenticatorResponse.data.ssoUrl);
Uri tokenUrl = new Uri(authenticatorResponse.data.tokenUrl);
logger.Debug("step 2: verify urls fetched from step 1");
logger.Debug("Checking sso url");
VerifyUrls(ssoUrl, oktaUrl);
logger.Debug("Checking token url");
VerifyUrls(tokenUrl, oktaUrl);
logger.Debug("step 3: get idp onetime token");
IdpTokenRestRequest idpTokenRestRequest = BuildIdpTokenRestRequest(tokenUrl);
var idpResponse = await session.restRequester.PostAsync <IdpTokenResponse>(idpTokenRestRequest, cancellationToken).ConfigureAwait(false);
string onetimeToken = idpResponse.CookieToken;
logger.Debug("step 4: get SAML reponse from sso");
var samlRestRequest = BuildSAMLRestRequest(ssoUrl, onetimeToken);
var samlRawResponse = await session.restRequester.GetAsync(samlRestRequest, cancellationToken).ConfigureAwait(false);
samlRawHtmlString = await samlRawResponse.Content.ReadAsStringAsync().ConfigureAwait(false);
logger.Debug("step 5: verify postback url in SAML reponse");
VerifyPostbackUrl();
logger.Debug("step 6: send SAML reponse to snowflake to login");
await base.LoginAsync(cancellationToken);
}
/// <see cref="IAuthenticator"/>
async Task IAuthenticator.AuthenticateAsync(CancellationToken cancellationToken)
{
logger.Info("Okta Authentication");
logger.Debug("step 1: get sso and token url");
var authenticatorRestRequest = BuildAuthenticatorRestRequest();
var authenticatorResponse = await session.restRequester.PostAsync <AuthenticatorResponse>(authenticatorRestRequest, cancellationToken);
authenticatorResponse.FilterFailedResponse();
Uri ssoUrl = new Uri(authenticatorResponse.data.ssoUrl);
Uri tokenUrl = new Uri(authenticatorResponse.data.tokenUrl);
logger.Debug("step 2: verify urls fetched from step 1");
logger.Debug("Checking sso url");
VerifyUrls(ssoUrl, oktaUrl);
logger.Debug("Checking token url");
VerifyUrls(tokenUrl, oktaUrl);
logger.Debug("step 3: get idp onetime token");
IdpTokenRestRequest idpTokenRestRequest = BuildIdpTokenRestRequest(tokenUrl);
var idpResponse = await session.restRequester.PostAsync <IdpTokenResponse>(idpTokenRestRequest, cancellationToken);
string onetimeToken = idpResponse.CookieToken;
logger.Debug("step 4: get SAML reponse from sso");
var samlRestRequest = BuildSAMLRestRequest(ssoUrl, onetimeToken);
var samlRawResponse = await session.restRequester.GetAsync(samlRestRequest, cancellationToken);
var samlRawHtmlString = await samlRawResponse.Content.ReadAsStringAsync();
logger.Debug("step 5: verify postback url in SAML reponse");
VerifyPostbackUrl(samlRawHtmlString);
logger.Debug("step 6: send SAML reponse to snowflake to login");
var loginRestRequest = BuildOktaLoginRestRequest(samlRawHtmlString);
var authnResponse = await session.restRequester.PostAsync <LoginResponse>(loginRestRequest, cancellationToken);
session.ProcessLoginResponse(authnResponse);
}
protected override async Task <HttpResponseMessage> SendAsync(HttpRequestMessage requestMessage,
CancellationToken cancellationToken)
{
HttpResponseMessage response = null;
int backOffInSec = 1;
ServicePoint p = ServicePointManager.FindServicePoint(requestMessage.RequestUri);
p.Expect100Continue = false; // Saves about 100 ms per request
p.UseNagleAlgorithm = false; // Saves about 200 ms per request
p.ConnectionLimit = 20; // Default value is 2, we need more connections for performing multiple parallel queries
TimeSpan httpTimeout = (TimeSpan)requestMessage.Properties["TIMEOUT_PER_HTTP_REQUEST"];
CancellationTokenSource childCts = null;
UriUpdater updater = new UriUpdater(requestMessage.RequestUri);
while (true)
{
try
{
childCts = null;
if (!httpTimeout.Equals(Timeout.InfiniteTimeSpan))
{
childCts = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken);
childCts.CancelAfter(httpTimeout);
}
response = await base.SendAsync(requestMessage, childCts == null?
cancellationToken : childCts.Token).ConfigureAwait(false);
}
catch (Exception e)
{
if (cancellationToken.IsCancellationRequested)
{
logger.Debug("SF rest request timeout.");
cancellationToken.ThrowIfCancellationRequested();
}
else if (childCts != null && childCts.Token.IsCancellationRequested)
{
logger.Warn("Http request timeout. Retry the request");
}
else
{
//TODO: Should probably check to see if the error is recoverable or transient.
logger.Warn("Error occurred during request, retrying...", e);
}
}
if (response != null)
{
if (response.IsSuccessStatusCode)
{
return(response);
}
logger.Debug($"Failed Response: {response.ToString()}");
}
else
{
logger.Info("Response returned was null.");
}
requestMessage.RequestUri = updater.Update();
logger.Debug($"Sleep {backOffInSec} seconds and then retry the request");
Thread.Sleep(backOffInSec * 1000);
backOffInSec = backOffInSec >= 16 ? 16 : backOffInSec * 2;
}
}