開源日報 每天推薦一個 GitHub 優質開源項目和一篇精選英文科技或編程文章原文，堅持閱讀《開源日報》，保持每日學習的好習慣。
今日推薦開源項目：《它不會直接教你玩攝影 awesome-OpenSourcePhotography》
今日推薦英文原文：《Understanding SQL Injections》
今日推薦開源項目：《它不會直接教你玩攝影 awesome-OpenSourcePhotography》傳送門：GitHub鏈接
推薦理由：一個關於攝影方面知識和工具的集合——說是這麼說，但是它並不會教你如何拍照就是了。準確的說這個項目中提供了很多關於照片和視頻方面的知識和工具——包括處理照片和視頻以及照片管理等等，當然了也包括一些工具和庫。如果你需要處理一些與照片和視頻有關的工作的話，興許這裡可以找到你想要的東西。
今日推薦英文原文：《Understanding SQL Injections》作者：Scott Cosentino
原文鏈接：https://medium.com/@scottc130/understanding-sql-injections-47049fcf9acb
推薦理由：關於一個常見的漏洞——SQL注入，如果有在使用資料庫的話就需要想辦法防止這種情況的產生了。

Understanding SQL Injections

Code injection vulnerabilities are rated as the most common vulnerability according to the OWASP 2017 Top 10 List. Most commonly, SQL injections are used to compromise databases and applications, in order to cause data leaks and unauthorized access. As someone involved in the field of tech, it is essential that you understand this vulnerability, so you can actively prevent it from happening to your applications.

To best understand the vulnerability, let』s build a sample database and application, and see how SQL interacts with the typical application. Suppose we have a simple SQL database, with a table called users, defined below:
Query to create user table
In this table, we will insert some sample users to have some data to work with:
Query to insert users and hashed passwords
We now have a database similar to one that might be used for authentication on any application. To see how we can interact with the database, I will create a basic VB.net application. Here we have a simple login form:
Login form for application
And some code that will take input from the form and check if the username and password supplied exist in the database: 
Dim sqlCommand as SqlCommand

Dim username As String
Dim password As String

username = usernameTextbox.Text
password = passwordTextbox.Text

sqlConnection.Open()
sqlCommand = New SqlCommand("SELECT COUNT(*) FROM Users WHERE Username = '" & username & "' AND userPassword = HASHBYTES('SHA1','" & password & "')", sqlConnection)

If sqlCommand.ExecuteScalar() > 0 Then
   MsgBox("Login Successful")
Else
   MsgBox("Login Failed")
End If
Now, we can test the application to see how the SQL query is built and executed against the database. If we add a breakpoint, we can step through the application to see the values of each of the variables to better understand what is happening.

To start, the user inputs their credentials, and presses 「Login」
User inputs credentials
Once login is pressed, the SQL query to check the user is built by concatenating the username and password inputs into a query, and running it against the database. The end SQL query looks like this: 
SELECT COUNT(*) FROM Users
WHERE Username = 'test1'
AND userPassword = HASHBYTES('SHA1','password')
The important thing to note is how this query is built. The user input is concatenated directly into the query, then the query is executed against the database. With the current query, the execution would yield a result of 1, given that there is one record matching the username and password, and this is ok and expected behavior. But what if a user inputted the character 『 into the username?
User inputs 『 as username
Now, the query would be built as: 
SELECT COUNT(*) FROM Users
WHERE Username = '''
AND userPassword = HASHBYTES('SHA1','password')
If we let this execute agains the database, we get the following result:
SQL exception for query
We get an SQL exception telling us that our query syntax is incorrect! So how is this possible? When the user inputs the 『 character, it is concatenated to the query, and the query is run against the database. SQL is interpreting the 『 character as a closing quote for the username string, causing a mismatch in quotes and therefore a syntax error in the query.

Already, this situation is bad, as the user can cause the program to crash just by entering a single character. However, it gets worse. The user can do a lot more damage by altering the query completely.

Consider the input shown below:
Malicious user input
If the attacker were to enter these values, the query that results from it would be: 
SELECT COUNT(*) FROM Users 
WHERE Username = ''
OR 1 = 1 -- '
AND userPassword = HASHBYTES('SHA1','notapassword')
Let』s break this query down to understand it. The filter is going to get all the situations where Username = 『』, and 1 =1. Since 1 will always equal 1, this will return all of the records. The final piece of the input, the two dashes ( — ) is a comment in SQL. Everything after those two characters is ignored.

The result of this query is to return the count of everything in the Users database. Since this is going to be greater than 1, the user will be authenticated with access, even though they did not enter valid credentials!
User is given access
This is obviously bad news, as we never want a user to be able to gain access to an application without proper credentials. So, how do we avoid this issue?

Some people might suggest removing any 『 character, however this might be a valid input (consider a name like O』Reily). This being the case, there is a better solution.

The best answer to this problem is parameterized queries. Parameterized queries will generate the SQL execution plan before input is added, meaning it will avoid executing code inputted by the user. If we wanted to modify our original code to use parameterized queries, we could do so as follows: 
Dim sqlCommand As SqlCommand

Dim query As String = "SELECT COUNT(*) FROM Users WHERE Username = @username AND userPassword = HASHBYTES('SHA1',@password)"

Dim username As String
Dim password As String

username = usernameTextbox.Text
password = passwordTextbox.Text

sqlConnection.Open()

sqlCommand = New SqlCommand(query, sqlConnection)

sqlCommand.Parameters.Add("@username", SqlDbType.VarChar, 300).Value = username

sqlCommand.Parameters.Add("@password", SqlDbType.VarChar, 300).Value = password

If sqlCommand.ExecuteScalar() > 0 Then

MsgBox("Login Successful")

Else

MsgBox("Login Failed")

End If
Doing this will solve our issue
User is now rejected access
The parameterized version of the query is significantly more secure, and will help you be safe from SQL injection attacks. It is important to use this method when accessing SQL databases, otherwise your data may be destroyed or improperly accessed by attackers!
下載開源日報APP：https://openingsource.org/2579/
加入我們：https://openingsource.org/about/join/
關注我們：https://openingsource.org/about/love/