GooglePrettify

2018年4月25日 星期三

MSSQL Unix timestamp conversion

So here is a quick function that turns a unix timestamp into a datetime and to convert a datetime to a unix timestamp. This is a reasonably easy task to perform with the builtin mssql date functions. Since a unix timestamp is the number of second since the 1st January 1970 we simply add these together.
To get a unix timestamp works in exactly the same way in reverse we simply find the number of seconds since 1st January 1970 using datediff. Its best to use these builtin functions because they will take care of the extra days in leap years for us.
CREATE FUNCTION [dbo].[UNIXToDateTime] (@timestamp int)
        RETURNS datetime
AS
BEGIN
        DECLARE @ret datetime

        SELECT @ret = DATEADD(second, @timestamp, '1970/01/01 00:00:00')

        RETURN @ret
END
GO

CREATE FUNCTION [dbo].[DateTimeToUNIX] (@date datetime)
        RETURNS int
AS
BEGIN
        DECLARE @ret int

        SELECT @ret = DATEDIFF(second, '1970/01/01 00:00:00', @date)

        RETURN @ret
END
GO

An example on how to use the functions above

SELECT dbo.UNIXToDateTime(dbo.DateTimeToUNIX(GETDATE()))

SELECT dbo.DateTimeToUNIX(GETDATE())

from : https://www.stev.org/post/mssqlconvertunixtimestamptodatetime

How To Get The Current Epoch Time (Unix Timestamp)

Perltime
PHPtime()
RubyTime.now (or Time.new). To display the epoch: Time.now.to_i
Pythonimport time first, then int(time.time())
Javalong epoch = System.currentTimeMillis()/1000;
Microsoft .NET C#epoch = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
VBScript/ASPDateDiff("s", "01/01/1970 00:00:00", Now())
Erlangcalendar:datetime_to_gregorian_seconds(calendar:now_to_universal_time( now()))-719528*24*3600.
OR
element(1, now()) * 10000 + element(2, now()).
MySQLSELECT unix_timestamp(now())
PostgreSQLSELECT extract(epoch FROM now());
Oracle PL/SQLSELECT (SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) *
24 * 60 * 60 FROM DUAL
SQL ServerSELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())
JavaScriptMath.round(new Date().getTime()/1000.0)getTime() returns time in milliseconds.
Unix/Linux Shelldate +%s
PowerShellGet-Date -UFormat "%s" Produces: 1279152364.63599
Actionscript(new Date()).time
Other OS’sCommand line: perl -e "print time" (If Perl is installed on your system)
ColdFusion (CFML) MX 6.1+#int( getTickCount() / 1000 )#
BashCommand Line: date +%s

from : https://shafiqissani.wordpress.com/2010/09/30/how-to-get-the-current-epoch-time-unix-timestamp/

2018年4月22日 星期日

Web 應用程式安全性威脅概觀 - STRIDE

如果任何其他不知名的使用者可以存取您的 Web 應用程式,那麼惡意使用者嘗試取得應用程式未經授權之存取權的可能性,便相當的高。可以在網際網路上可公開存取的伺服器,也是經常被惡意使用者探測是否有任何安全性的弱點。因此,建議您對所有 Web 應用程式採取預防措施並且進行適當的安全性建置。
如需撰寫安全程式碼和保護應用程式安全性之最佳作法的詳細資訊,請參閱 Michael Howard 和 David LeBlanc 所著的《撰寫安全的程式碼》(英文),或者查閱 Microsoft 典範與實例所提供的指南。

實作安全性只是方案的一部分,另一個重點便是隨時保持警戒,即使系統已建置多種安全性保護措施,仍然需要透過下列幾種方式密切注意:
  • 監視系統的事件記錄檔。留意不斷要求登入系統的嘗試,或是對 Web 伺服器所做的過度要求。
  • 隨時進行 Microsoft Windows 和網際網路資訊服務 (IIS) 的安全性更新,並且更新 Microsoft SQL Server 或是應用程式可能會使用的其他資料來源,讓應用程式伺服器保持在最新狀態。

開發更安全應用程式的要點之一在於認識對應用程式的可能威脅;Microsoft 已開發可將威脅分類的方法:欺騙、竄改、否認、資訊洩露、拒絕服務、提高權限 (STRIDE)。以下章節會簡要說明這些威脅,以及這些威脅入侵 Web 應用程式的方法。

欺騙

欺騙」(Spoofing) 是指以未授權的方式模擬使用者或處理序。欺騙最簡單的方法就是輸入不同使用者的憑證。惡意使用者可能也會變更 Cookie 的內容,偽裝成另一名使用者,或是假裝 Cookie 來自不同的伺服器。
一般而言,您可以使用嚴格驗證 (Authentication) 避免發生假冒身分。只要有人要求存取非公用資訊時,即需確認他們的身分。將認證資訊存放在安全的地方,有助於防範發生任何欺騙的行為。例如,不要將密碼或其他機密資訊放在 Cookie 中,讓惡意使用者可在其中輕易找到並進行修改。

竄改

竄改」(Tampering) 的意思是未經授權變更或刪除資源。其中一項範例是毀壞網頁,惡意使用者進入您的網站並變更檔案。竄改的間接方法是「指令碼攻擊」(Script Exploit)。惡意使用者能夠取得要執行的程式碼 (指令碼),方法是將其偽裝成頁面的使用者輸入或連結。
防範竄改的主要措施是使用 Windows 安全性鎖定檔案、目錄和其他 Windows 資源,此外,也應該以最小權限執行應用程式。只要不信任來自任一使用者甚至是資料庫的所有資訊,即可防止指令碼遭到入侵。只要從未受信任的來源取得資訊,一定要確認它不含任何可執行的程式碼。

否認

否認」(Repudiation) 的威脅在於交易中含有主體時,以未經證實的方法執行交易。在 Web 應用程式中,這可能表示模擬無辜使用者的認證。您可以使用嚴格驗證方式,防範否認性攻擊。此外,使用 Windows 的登入功能,將伺服器上任何活動的稽核記錄保留下來。

資訊洩露

資訊洩露」(Information Disclosure) 表示竊取或洩露原本應該是私用的資訊。最常見的範例是竊取密碼,但也可能涉及對伺服器上任何檔案或資源的存取。
對於資訊洩露的最佳防範措施就是沒有可洩露的資訊。例如,如果不儲存密碼,惡意使用者就無法竊取另一個儲存密碼的替代方式是只儲存密碼的雜湊,當使用者出示認證時,可將使用者密碼製作雜湊,並只比較兩者的雜湊。如果要儲存敏感性資訊,請使用 Windows 安全性確保敏感性資訊的安全。當然,您還應該使用驗證,確保只有經過授權的使用者能夠存取受限制的資訊。如果必須公開敏感性資訊,建議您在儲存時加密該資訊,並在瀏覽器之間傳送資訊時使用 Secure Sockets Layer (SSL) 加密資訊。

拒絕服務

拒絕服務」(Denial of Service) 攻擊是故意造成應用程式的可用性降低,使其無法達到其應有的水準。常見的範例是造成 Web 應用程式的超載,該它無法正常的提供使用者服務。也有可能是,惡意使用者嘗試損毀您的伺服器。
IIS 可讓您「壓制」(Throttle) 應用程式,限制應用程式可以對要求提供服務的數目;您也可以拒絕已知為惡意的使用者或 IP 位址的存取。如果要應用程式保持在連線狀況,則必須執行相當穩固的程式碼。您應該徹底測試應用程式,在可行的範圍內適當地回應錯誤狀況。

提高權限

提高權限」(Elevation of Privilege) 攻擊是指使用惡意的手段,取得超出一般所指派的權限。例如,如果提高權限攻擊成功,惡意使用者就可以取得 Web 伺服器的系統管理權限、任意存取伺服器上的資料,並控制伺服器的功能。
若要協助防範提高權限的攻擊,可行的話,以最小的權限內容執行應用程式。例如,建議您不要以系統 (系統管理員) 使用者的身分執行 ASP.NET 應用程式。

from : https://msdn.microsoft.com/zh-tw/library/f13d73y6(v=vs.100).aspx

2018年4月21日 星期六

安全要素与 STRIDE 威胁

STRIDE 威胁,代表六种安全威胁:身份假冒(Spoofing)、篡改(Tampering)、抵赖(Repudiation)、信息泄露(Information Disclosure)、拒绝服务(Denial of Service)、特权提升(Elevation of Privilege)。

身份假冒(Spoofing)

身份假冒,即伪装成某对象或某人。例如,我们通过伪造别人的 ID 进行操作。

篡改(Tampering)

篡改,即未经授权修改数据或者代码。例如,我通过网络抓包或者某种途径修改某个请求包,而服务端没有进行进一步的防范措施,使得我篡改的请求包提交成功。

抵赖(Repudiation)

抵赖,即拒绝执行他人无法证实也无法反对的行为而产生抵赖。例如,我攻击了某个产品,他们并不知道是我做的,没有证据证明是我做的,我就可以进行抵赖,换句话说,我可以死不承认。

信息泄露(Information Disclosure)

信息泄露,即将信息暴露给未授权用户。例如,我通过某种途径获取未经加密的敏感信息,例如用户密码。

拒绝服务(Denial of Service)

拒绝服务,即拒绝或降低有效用户的服务级别。例如,我通过拒绝服务攻击,使得其他正常用户无法使用产品的相关服务功能。

特权提升(Elevation of Privilege)

特权提升,即通过非授权方式获得更高权限。例如,我试图用管理员的权限进行业务操作。

安全要素

为了防范上面的 STRIDE 威胁,我们需要采用一些防范措施。
威胁 安全要素 消减技术
身份假冒 认证 Kerberos、SSL/TLS、证书、认证码等
篡改 完整性 访问控制列表、SSL/TLS、认证码等
抵赖 非抵赖/审计/记录 安全审计和日志记录、数字签名、可信第三方
信息泄露 保密 加密、访问控制列表
拒绝服务 可用性 访问控制列表、过滤、配额、授权
特权提升 授权 访问控制列表、角色控制、授权
(完)

作者:梁桂钊
链接:https://juejin.im/post/58c6a7690ce46300547ceabc
来源:掘金
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

from : https://juejin.im/post/58c6a7690ce46300547ceabc

2018年4月17日 星期二

.Net Exceptions Best Practices

Exception is one of those constructs that is easy to misuse. This might include not throwing exception when one should or catching the exception without a good reason. Also there is the problem of throwing the wrong exception which not only doesn’t help us, but can confuse us. On the other hand there is the problem of properly handling the exception. Exception handling is even worse when it comes to improper use. So in this post I’m going to discuss some best practices regarding throwing and handling exceptions. I’ll show you how throwing the proper exception can save us a lot of headaches in terms of debugging. I also going to discuss how bad exception handling can be misleading when we want find bugs.

Throwing Exception

When To Throw Exception

There are many circumstances when throwing exception makes sense, in this section I’ll describe them and discuss why it’s a good idea to throw them. Please note that a lot of examples in this article is simplified to prove a point. For example one does not use a method to retrieve an array element. Or in some cases I didn’t use the previously advocated techniques to focus on the current point. So naturally examples doesn’t try to be a perfect form of exception handling in every way, because that introduce extra elements that could potentially distract the reader.

1 – Completing The Process And Giving Result Is Impossible (Fail Fast)

static void FindWinner(int[] winners)
{
if (winners == null)
{
throw new System.ArgumentNullException($"Parameter {nameof(winners)} cannot be null", nameof(winners));
}
OtherMethodThatUsesTheArray(winner);
}
Suppose we have the above method, here we throw an exception because getting result from this method without the existence of winner array is impossible. One other important point is the ease of use for the user of this method. Imagine we didn’t throw an exception and the array passed into OtherMethodThatUsesTheArray method and that method throws a NullReferenceException. By not throwing an exception debugging becomes much harder. Because the debugger of this code must first look into the OtherMethodThatUsesTheArray  method because that’s where the error originated form. Then he figures out that the winner argument is where this error is originated. When we throw an exception we know exactly where is error is originated from and we don’t have to chase it around the code base. Another problem is unnecessary use of resource, suppose before we reach the point that an exception happens by the framework, we do some expensive processing. Now if the method cannot give us our result without the dependent parameters or what have you, we wasted a lot of resource processing when in fact the method couldn’t possibly succeed in the first place. Remember we don’t throw exception when an error might happen, but we do when the error is blocking the process. Also sometimes we could avoid using exception and use try-stuff pattern like int.TryParse and not throwing exception. Note that exceptions are much more expensive than patterns like the one I just mentioned. Also take a look at fail-fast paradigm published by Martin Fowler (and written by Jim Shore).

2 – Calling an object’s member might produce invalid results given the object current state or might completely fail

void WriteLog(FileStream logFile)
{
if (!logFile.CanWrite)
{
throw new System.InvalidOperationException("Logfile cannot be read-only");
}
// Else write data to the log and return.
}
view rawWriteLog.cs hosted with ❤ by GitHub
Here the file stream that is passed to the WriteLog method crated in such a way that is not writable. In this instance we know that this method is not going to work. Because we can’t log to a file that is not writable. Another example is when we have a class that we expect to be in specific state when we receive it. We have again fail fast by throwing an exception and save resources and debugging time.

3 – Catching A Generic Non-Specific Exception And Throwing A More Specific One

static int GetValueFromArray(int[] array, int index)
{
try
{
return array[index];
}
catch (System.IndexOutOfRangeException ex)
{
throw new ArgumentOutOfRangeException("Index is out of range", "index", ex);
}
}
There is one rule of thumb when it comes to exceptions, the more specific exception our program produce, the easier it is to debug and maintain. In other word by doing this our program produce more accurate errors. So we should always strive to throw more specific exceptions as much as possible. That’s why throwing exceptions like System.ExceptionSystem.SystemExceptionSystem.NullReferenceException, or System.IndexOutOfRangeException is not a good idea. It’s better to not use them and see them as a signal that the error is generated by the framework and we’re going to have a tough time debugging. In the code above you see that we catch the IndexOutOfRangeException and throw a new ArgumentOutOfRangeException  that shows us where the actual error is originated from. We can also use it to catch the framework generated exception and throw a new custom exception. That allows us to add additional information or maybe handle it differently. Just make sure you pass the original exception into the custom exception as inner exception, otherwise the stacktrace will be lost.

4 – Throw Exception For Exceptional Cases

This one might sound obvious, but it can be tricky sometimes. There is somethings in our program that are expected to happen and we can’t count them as errors. So we’ll not throw an exception. For example a search query might return empty or the user login attempt might fail. In these circumstances it’s better to return some kind of meaningful message then throwing exception. As Steve McConnell puts it in Code Complete book, “Exceptions should be reserved for what’s truly exceptional” as opposed to expected ones.

Do Not Use Exceptions To Change The Flow Of The Program

Take the below code for example.
[HttpPost]
public ViewResult CreateProduct(CreateProductViewModel viewModel)
{
try
{
ValidateProductViewModel(viewModel);
CreateProduct(viewModel);
}
catch (ValidationException ex)
{
return View(viewModel);
}
}
view rawCreateProduct.cs hosted with ❤ by GitHub
This is a pattern that I see in some legacy code that I need to work on. As you can see the method ValidateProductViewModel throws an exception for some reason if the view model was not valid. Then if the view model was not valid it catches it and return the view with errors. We better change the code above to the code below?!
[HttpPost]
public ViewResult CreateProduct(CreateProduct viewModel)
{
bool viewModelIsValid = ValidateProductViewModel(viewModel);
if(!viewModelIsValid) return View(viewModel);
CreateProduct(viewModel);
return View(viewModel);
}
view rawCreateProduct.cs hosted with ❤ by GitHub
Here, the method that is responsible for validation returns a bool instead of throwing exception if the view model was not valid. Here’s a good question on Stackoverflow about this.

Do Not Return Error Code, Throw Exception Instead

Throwing exception is always safer than return an error code. The reason is what if the calling code forgot to check for or returned error code and went ahead with execution? But that can’t happen if we throw an exception.

Make Sure To Clean Up Any Side Effect If Exception Thrown

private static void MakeDeposit(Account account,decimal amount)
{
try
{
account.Deposit(amount);
}
catch
{
account.RollbackDeposit(amount);
throw;
}
}
view rawMakeDeposit.cs hosted with ❤ by GitHub
Here we know that an error might happen when calling the deposit method. We should make sure if an exception happens, any changes to the system is rolled back.
try
{
DBConnection.Save();
}
catch
{
// Roll back the DB changes so they aren't corrupted on ANY exception
DBConnection.Rollback();
// Re-throw the exception, it's critical that the user knows that it failed to save
throw;
}
view rawDBConnection.cs hosted with ❤ by GitHub
You can also use transaction scope instead of handling it this way. Remember you can do it in finally block too.

If You Catch An Exception And Cannot Handle It Properly, Make Sure To Re-Throw It

There are circumstances when we catch an exception but we don’t intend to handle it, maybe we just want to log it. Something Like:
try
{
conn.Close();
}
catch (InvalidOperationException ex)
{
_logger.LogError(ex.Message, ex);
//bad practice, stack trace is lost
//throw ex;
//good practice, keepi'n the stack trace
throw;
}
view rawReThrow.cs hosted with ❤ by GitHub
As you can see in the code above, not only we should re-throw the exception, but also we should re-throw it in a way that we don’t loose the stack trace. In this case if we use throw ex, we’ll lose the stacktrace, but if we use throw without an instace ex in front of it, we preserved the stackstrace.

Do Not Use Exceptions As Parameter Or Return Value

Using Exception as argument or return value doesn’t make sense most of the time. Maybe the only time that it might make sense is when we use it in an exception factory, something like.
Exception AnalyzeHttpError(int errorCode) {
if (errorCode < 400) {
throw new NotAnErrorException();
}
switch (errorCode) {
case 403:
return new ForbiddenException();
case 404:
return new NotFoundException();
case 500:
return new InternalServerErrorException();
default:
throw new UnknownHttpErrorCodeException(errorCode);
}
}
view rawAnalyzeHttpError.cs hosted with ❤ by GitHub

Prevent The Program From Throwing Exception If Possible, Exceptions Are Expensive

try
{
conn.Close();
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.GetType().FullName);
Console.WriteLine(ex.Message);
}
view rawCatch.cs hosted with ❤ by GitHub
Take the above code for example, we put the code for closing the connection in try block. If the connection is already closed, it’ll throws an exception. But maybe we could achieve the same thing without causing the program to throw exception?
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
view rawWithoutException.cs hosted with ❤ by GitHub
As you can see the try block was unnecessary, it caused the program to throw an exception if the connection was already closed, and exception are more expensive than if check.

Create Your Own Exception Class

Not all exceptions that can happen are covered by the framework. Sometimes we need to create our own exception type. They can be defined as class, like any other classes in C#. We create custom exception classes usually because we want to handle that exception differently. Or that specific kind of exception is very critical to our application. To create a custom exception we need to create a class that is derived from System.Exception.
[Serializable()]
public class InvalidDepartmentException : System.Exception
{
public InvalidDepartmentException() : base() { }
public InvalidDepartmentException(string message) : base(message) { }
public InvalidDepartmentException(string message, System.Exception inner) : base(message, inner) { }
// A constructor is needed for serialization when an exception propagates from a remoting server to the client.
protected InvalidDepartmentException(SerializationInfo info, StreamingContext context) { }
}
Here the The derived class defined four constructors. one default constructor, one that sets the message property, and one that sets both the Message and InnerException. The fourth constructor is used to serialize the exception. Also note that exceptions should be serializable.

Handling (Catching) Exception

When To Catch Exception

Catching exception is even more misused than throwing it. But this kind of misuse can lead to a lot of pain when the application reached its maintenance phase. In the following section I’ll describe some best practices regarding handling exceptions.

1 – Beware Of Pokemon Exception Handling

I saw in a lot of applications, the try block is used to suppress the exception. But that’s not what’s try-catch block is made for.  By using try like this, we change the behavior of our system and making finding bugs harder than it should be. That so common that we have a term for its misuse, namely Pokemon exception handling. Most of the time the thing that happens is that the try-catch block swallows the error and the error end-up somewhere else in our application than its original location. The real pain is that most of the time the error message doesn’t make any sense at all, because it’s not where the original error is coming from. This make for a frustrating debugging experience.

2 – Use Try Block When You can actually handle the exception and recover from it

One good example of this scenario is when the program prompt user for a path to a file and file doesn’t exist. We can recover from this if the application throws an error, maybe by catching the exception and asking the user to enter another file path. For this reason you should order catch block from the most specific to the least specific one.
public void OpenFile(string filePath)
{
try
{
File.Open(path);
}
catch (FileNotFoundException ex)
{
Console.WriteLine("The specified file path not found, please enter another path.");
PromptUserForAnotherFilePath();
}
}
view rawOpenFile.cs hosted with ❤ by GitHub
One other thing you can use is exception filter. Exception filter works like if statements for a catch block. If the check evaluate to true, the catch block executed, otherwise the catch block is ignored.
private static void Filter()
{
try
{
A();
}
catch (OperationCanceledException exception) when (string.Equals(nameof(ExceptionFilter), exception.Message, StringComparison.Ordinal))
{
}
}
view rawFilter.cs hosted with ❤ by GitHub

3 – You Want To Catch A Generic Exception And Throw A More Specific One With More Context

int GetInt(int[] array, int index)
{
try
{
return array[index];
}
catch(System.IndexOutOfRangeException e)
{
throw new System.ArgumentOutOfRangeException("Parameter index is out of range.", e);
}
}
view rawGetInt.cs hosted with ❤ by GitHub
Take the above code for instance. Here we catch the IndexOutOfBound exception and throw ArgumentOutOfRangeException. By doing this we have more context about where the error originated from and can find the source of our problem more quickly.

4 – You Want To Partially Handle The Exception And Pass It Up For Further Processing

try
{
// Open File
}
catch (FileNotFoundException e)
{
_logger.LogError(e);
// Re-throw the error.
throw;
}
In the above example, we catch the exception, we log the information that we want about it and then we re-throw the exception.

5 – Swallow Exception Only At The Highest Layer Of Your Application

In every application, there is some point when exception should be swallowed. For example most of the time in web applications we don’t want our user to see the exception error. We want to show something generic to the user and keep the exception information for debugging purposes. What we can do in these circumstances is that we wrap the code block in try-catch block. If an error happens, we catch the exception, we log the error and return some generic information to the user, something like the code below.
[HttpPost]
public async Task<IActionResult> UpdatePost(PostViewModel viewModel)
{
try
{
_mediator.Send(new UpdatePostCommand{ PostViewModel = viewModel});
return View(viewModel);
}
catch (Exception e)
{
_logger.LogError(e);
return View(viewModel);
}
}
view rawUpdatePost.cs hosted with ❤ by GitHub
Notice that in these circumstances we still log the error, but the error cannot bubble up the layer. Because this is the last layer, so there is no layer above it actually. In other word the only code that should consume an exception and not re-throw it should be the UI or public API. Some developer prefer to configure some global way of handling exceptions that occurring in this layer. You can take a look at my previous post that uses such a technique. The most important things when it comes to exception handling is that exception handling should never hide issues.

Finally The Finally Block

Finally block is used for cleaning up any remaining resources used in try block without waiting for the garbage collector in runtime to finalize the object. We can use it to close database connection, file streams etc. Notice that we first check to see if FileStream object is null before calling close. Without doing this the finally block could throw its own exception which is not a good thing at all.
FileStream file = null;
var fileinfo = new FileInfo("C:\\file.txt");
try
{
file = fileinfo.OpenWrite();
file.WriteByte(0xF);
}
finally
{
// Check for null because OpenWrite might have failed.
if (file != null)
{
file.Close();
}
}
view rawFinally.cs hosted with ❤ by GitHub
We can use it with or without catch block, the important point is finally block is always going to be executed, regardless of whether or not an exception happened. Another important point is that because resources like database connection is so expensive, it’s better to close them in finally block as soon as possible, as opposed to waiting for garbage collector to do it for us. We can also use the using statement since the FileStream is Implementing IDisposable. Worth to note that using statement is just a syntactic sugar that translates to try and finally block and is more readable and overall is a better choice.

Summary

In this post, I described some best practices regarding exceptions. I discussed in what situations it’s a good idea to throw exception and also described some useful guideline for doing it. We also saw how to handle exception and when and why to handle them. We also saw how misusing exception can negatively effect the ability to find and track bugs in our application.

from : http://hamidmosalla.com/2018/02/28/net-exception-best-practices/