Injeksi SQL
Injeksi SQL
Injeksi SQL adalah teknik injeksi kode yang dapat merusak basis data Anda.
Injeksi SQL adalah salah satu teknik peretasan web yang paling umum.
Injeksi SQL adalah penempatan kode berbahaya dalam pernyataan SQL, melalui input halaman web.
SQL di Halaman Web
Injeksi SQL biasanya terjadi ketika Anda meminta input dari pengguna, seperti nama pengguna / pengguna mereka, dan alih-alih nama / id, pengguna memberi Anda pernyataan SQL bahwa Anda tanpa sadar akan berjalan di basis data Anda.
Lihatlah contoh berikut yang membuat pernyataan SELECT dengan menambahkan variabel (txtUserId) ke string yang dipilih. Variabel diambil dari input pengguna (getRequestString):
Contoh
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
Sisa bab ini menjelaskan potensi bahaya menggunakan input pengguna dalam pernyataan SQL.
Injeksi SQL Berdasarkan 1 = 1 Selalu Benar
Lihatlah contoh di atas lagi. Tujuan asli kode adalah untuk membuat pernyataan SQL untuk memilih pengguna, dengan id pengguna yang diberikan.
Jika tidak ada yang mencegah pengguna memasukkan input "salah", pengguna dapat memasukkan beberapa input "pintar" seperti ini:
Identitas pengguna:
Kemudian, pernyataan SQL akan terlihat seperti ini:
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
SQL di atas adalah valid dan akan mengembalikan SEMUA baris dari tabel "Pengguna", karena OR 1 = 1 selalu BENAR.
Apakah contoh di atas terlihat berbahaya? Bagaimana jika tabel "Pengguna" berisi nama dan kata sandi?
Pernyataan SQL di atas hampir sama dengan ini:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
Seorang hacker mungkin mendapatkan akses ke semua nama pengguna dan kata sandi dalam database, dengan hanya memasukkan 105 ATAU 1 = 1 ke dalam kolom input.
Injeksi SQL Berdasarkan "" = "" Selalu Benar
Berikut adalah contoh login pengguna di situs web:
Nama pengguna:
Kata sandi:
Contoh
uName = getRequestString("username");
uPass = getRequestString("userpassword");
sql = 'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' + uPass + '"'
Hasil
SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass"
Seorang peretas mungkin mendapatkan akses ke nama pengguna dan kata sandi dalam database hanya dengan memasukkan "OR" "=" ke dalam nama pengguna atau kotak teks kata sandi:
Nama pengguna:
Kata sandi:
Kode di server akan membuat pernyataan SQL yang valid seperti ini:
Hasil
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""
SQL di atas adalah valid dan akan mengembalikan semua baris dari tabel "Users", karena OR "" = "" selalu BENAR.
Injeksi SQL Berdasarkan Pernyataan SQL Batched
Sebagian besar database mendukung pernyataan SQL batch.
Kumpulan pernyataan SQL adalah sekelompok dua atau lebih pernyataan SQL, dipisahkan oleh titik koma.
Pernyataan SQL di bawah ini akan mengembalikan semua baris dari tabel "Pengguna", lalu hapus tabel "Pemasok".
Contoh
SELECT * FROM Users; DROP TABLE Suppliers
Lihatlah contoh berikut:
Contoh
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
Dan input berikut:
Identitas pengguna:
Pernyataan SQL yang valid akan terlihat seperti ini:
Hasil
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;
Gunakan Parameter SQL untuk Perlindungan
Untuk melindungi situs web dari injeksi SQL, Anda dapat menggunakan parameter SQL.
Parameter SQL adalah nilai yang ditambahkan ke kueri SQL pada waktu eksekusi, secara terkontrol.
Contoh Razor ASP.NET
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);
Perhatikan bahwa parameter diwakili dalam pernyataan SQL oleh tanda @.
Mesin SQL memeriksa setiap parameter untuk memastikan bahwa itu benar untuk kolomnya dan diperlakukan secara harfiah, dan bukan sebagai bagian dari SQL yang akan dieksekusi.
Contoh lain
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);
Contohnya
Contoh berikut menunjukkan cara membuat kueri yang berparameter dalam beberapa bahasa web umum.
PERNYATAAN PILIHAN DI ASP.NET:
txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserID);
command.ExecuteReader();
Masukkan ke dalam pernyataan di ASP.NET:
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtNam);
command.Parameters.AddWithValue("@1",txtAdd);
command.Parameters.AddWithValue("@2",txtCit);
command.ExecuteNonQuery();
Masukkan ke dalam pernyataan di PHP:
$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City)
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();
SQL Hosting
SQL Hosting
Jika Anda ingin situs web Anda dapat menyimpan dan mengambil data dari database, server web Anda harus memiliki akses ke sistem database yang menggunakan bahasa SQL.
Jika server web Anda di-host oleh Penyedia Layanan Internet (ISP), Anda harus mencari paket hosting SQL.
Database hosting SQL yang paling umum adalah MS SQL Server, Oracle, MySQL, dan MS Access.
MS SQL Server
Microsoft's SQL Server adalah perangkat lunak basis data yang populer untuk situs web yang digerakkan oleh basis data dengan lalu lintas tinggi.
SQL Server adalah sistem basis data SQL yang sangat kuat, kuat, dan berfitur lengkap.
Peramal
Oracle juga merupakan perangkat lunak basis data yang populer untuk situs web yang digerakkan oleh basis data dengan lalu lintas tinggi.
Oracle adalah sistem basis data SQL yang sangat kuat, kuat, dan berfitur lengkap.
MySQL
MySQL juga merupakan perangkat lunak basis data yang populer untuk situs web.
MySQL adalah sistem basis data SQL yang sangat kuat, kuat, dan berfitur lengkap.
MySQL adalah alternatif murah untuk solusi Microsoft dan Oracle yang mahal.
Mengakses
Ketika situs web hanya membutuhkan database sederhana, Microsoft Access dapat menjadi solusi.
Akses tidak cocok untuk lalu lintas sangat tinggi, dan tidak sekuat MySQL, SQL Server, atau Oracle.
Referensi Kata Kunci SQL
Referensi kata kunci SQL ini berisi kata-kata yang dicadangkan dalam SQL.
Kata kunci SQL
Keyword Description ADD Adds a column in an existing table ADD CONSTRAINT Adds a constraint after a table is already created ALTER Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table ALTER COLUMN Changes the data type of a column in a table ALTER TABLE Adds, deletes, or modifies columns in a table ALL Returns true if all of the subquery values meet the condition AND Only includes rows where both conditions is true ANY Returns true if any of the subquery values meet the condition AS Renames a column or table with an alias ASC Sorts the result set in ascending order BACKUP DATABASE Creates a back up of an existing database BETWEEN Selects values within a given range CASE Creates different outputs based on conditions CHECK A constraint that limits the value that can be placed in a column COLUMN Changes the data type of a column or deletes a column in a table CONSTRAINT Adds or deletes a constraint CREATE Creates a database, index, view, table, or procedure CREATE DATABASE Creates a new SQL database CREATE INDEX Creates an index on a table (allows duplicate values) CREATE OR REPLACE VIEW Updates a view CREATE TABLE Creates a new table in the database CREATE PROCEDURE Creates a stored procedure CREATE UNIQUE INDEX Creates a unique index on a table (no duplicate values) CREATE VIEW Creates a view based on the result set of a SELECT statement DATABASE Creates or deletes an SQL database DEFAULT A constraint that provides a default value for a column DELETE Deletes rows from a table DESC Sorts the result set in descending order DISTINCT Selects only distinct (different) values DROP Deletes a column, constraint, database, index, table, or view DROP COLUMN Deletes a column in a table DROP CONSTRAINT Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint DROP DATABASE Deletes an existing SQL database DROP DEFAULT Deletes a DEFAULT constraint DROP INDEX Deletes an index in a table DROP TABLE Deletes an existing table in the database DROP VIEW Deletes a view EXEC Executes a stored procedure EXISTS Tests for the existence of any record in a subquery FOREIGN KEY A constraint that is a key used to link two tables together FROM Specifies which table to select or delete data from FULL OUTER JOIN Returns all rows when there is a match in either left table or right table GROUP BY Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG) HAVING Used instead of WHERE with aggregate functions IN Allows you to specify multiple values in a WHERE clause INDEX Creates or deletes an index in a table INNER JOIN Returns rows that have matching values in both tables INSERT INTO Inserts new rows in a table INSERT INTO SELECT Copies data from one table into another table IS NULL Tests for empty values IS NOT NULL Tests for non-empty values JOIN Joins tables LEFT JOIN Returns all rows from the left table, and the matching rows from the right table LIKE Searches for a specified pattern in a column LIMIT Specifies the number of records to return in the result set NOT Only includes rows where a condition is not true NOT NULL A constraint that enforces a column to not accept NULL values OR Includes rows where either condition is true ORDER BY Sorts the result set in ascending or descending order OUTER JOIN Returns all rows when there is a match in either left table or right table PRIMARY KEY A constraint that uniquely identifies each record in a database table PROCEDURE A stored procedure RIGHT JOIN Returns all rows from the right table, and the matching rows from the left table ROWNUM Specifies the number of records to return in the result set SELECT Selects data from a database SELECT DISTINCT Selects only distinct (different) values SELECT INTO Copies data from one table into a new table SELECT TOP Specifies the number of records to return in the result set SET Specifies which columns and values that should be updated in a table TABLE Creates a table, or adds, deletes, or modifies columns in a table, or deletes a table or data inside a table TOP Specifies the number of records to return in the result set TRUNCATE TABLE Deletes the data inside a table, but not the table itself UNION Combines the result set of two or more SELECT statements (only distinct values) UNION ALL Combines the result set of two or more SELECT statements (allows duplicate values) UNIQUE A constraint that ensures that all values in a column are unique UPDATE Updates existing rows in a table VALUES Specifies the values of an INSERT INTO statement VIEW Creates, updates, or deletes a view WHERE Filters a result set to include only records that fulfill a specified condition
Fungsi MySQL
MySQL memiliki banyak fungsi bawaan.
Referensi ini berisi string, numerik, tanggal, dan beberapa fungsi lanjutan di MySQL.
Fungsi String MySQL
Function Description ASCII Returns the ASCII value for the specific character CHAR_LENGTH Returns the length of a string (in characters) CHARACTER_LENGTH Returns the length of a string (in characters) CONCAT Adds two or more expressions together CONCAT_WS Adds two or more expressions together with a separator FIELD Returns the index position of a value in a list of values FIND_IN_SET Returns the position of a string within a list of strings FORMAT Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places INSERT Inserts a string within a string at the specified position and for a certain number of characters INSTR Returns the position of the first occurrence of a string in another string LCASE Converts a string to lower-case LEFT Extracts a number of characters from a string (starting from left) LENGTH Returns the length of a string (in bytes) LOCATE Returns the position of the first occurrence of a substring in a string LOWER Converts a string to lower-case LPAD Left-pads a string with another string, to a certain length LTRIM Removes leading spaces from a string MID Extracts a substring from a string (starting at any position) POSITION Returns the position of the first occurrence of a substring in a string REPEAT Repeats a string as many times as specified REPLACE Replaces all occurrences of a substring within a string, with a new substring REVERSE Reverses a string and returns the result RIGHT Extracts a number of characters from a string (starting from right) RPAD Right-pads a string with another string, to a certain length RTRIM Removes trailing spaces from a string SPACE Returns a string of the specified number of space characters STRCMP Compares two strings SUBSTR Extracts a substring from a string (starting at any position) SUBSTRING Extracts a substring from a string (starting at any position) SUBSTRING_INDEX Returns a substring of a string before a specified number of delimiter occurs TRIM Removes leading and trailing spaces from a string UCASE Converts a string to upper-case UPPER Converts a string to upper-case
Fungsi Numerik MySQL
Function Description ABS Returns the absolute value of a number ACOS Returns the arc cosine of a number ASIN Returns the arc sine of a number ATAN Returns the arc tangent of one or two numbers ATAN2 Returns the arc tangent of two numbers AVG Returns the average value of an expression CEIL Returns the smallest integer value that is >= to a number CEILING Returns the smallest integer value that is >= to a number COS Returns the cosine of a number COT Returns the cotangent of a number COUNT Returns the number of records returned by a select query DEGREES Converts a value in radians to degrees DIV Used for integer division EXP Returns e raised to the power of a specified number FLOOR Returns the largest integer value that is <= to a number GREATEST Returns the greatest value of the list of arguments LEAST Returns the smallest value of the list of arguments LN Returns the natural logarithm of a number LOG Returns the natural logarithm of a number, or the logarithm of a number to a specified base LOG10 Returns the natural logarithm of a number to base 10 LOG2 Returns the natural logarithm of a number to base 2 MAX Returns the maximum value in a set of values MIN Returns the minimum value in a set of values MOD Returns the remainder of a number divided by another number PI Returns the value of PI POW Returns the value of a number raised to the power of another number POWER Returns the value of a number raised to the power of another number RADIANS Converts a degree value into radians RAND Returns a random number ROUND Rounds a number to a specified number of decimal places SIGN Returns the sign of a number SIN Returns the sine of a number SQRT Returns the square root of a number SUM Calculates the sum of a set of values TAN Returns the tangent of a number TRUNCATE Truncates a number to the specified number of decimal places
Fungsi Tanggal MySQL
Function Description ADDDATE Adds a time/date interval to a date and then returns the date ADDTIME Adds a time interval to a time/datetime and then returns the time/datetime CURDATE Returns the current date CURRENT_DATE Returns the current date CURRENT_TIME Returns the current time CURRENT_TIMESTAMP Returns the current date and time CURTIME Returns the current time DATE Extracts the date part from a datetime expression DATEDIFF Returns the number of days between two date values DATE_ADD Adds a time/date interval to a date and then returns the date DATE_FORMAT Formats a date DATE_SUB Subtracts a time/date interval from a date and then returns the date DAY Returns the day of the month for a given date DAYNAME Returns the weekday name for a given date DAYOFMONTH Returns the day of the month for a given date DAYOFWEEK Returns the weekday index for a given date DAYOFYEAR Returns the day of the year for a given date EXTRACT Extracts a part from a given date FROM_DAYS Returns a date from a numeric datevalue HOUR Returns the hour part for a given date LAST_DAY Extracts the last day of the month for a given date LOCALTIME Returns the current date and time LOCALTIMESTAMP Returns the current date and time MAKEDATE Creates and returns a date based on a year and a number of days value MAKETIME Creates and returns a time based on an hour, minute, and second value MICROSECOND Returns the microsecond part of a time/datetime MINUTE Returns the minute part of a time/datetime MONTH Returns the month part for a given date MONTHNAME Returns the name of the month for a given date NOW Returns the current date and time PERIOD_ADD Adds a specified number of months to a period PERIOD_DIFF Returns the difference between two periods QUARTER Returns the quarter of the year for a given date value SECOND Returns the seconds part of a time/datetime SEC_TO_TIME Returns a time value based on the specified seconds STR_TO_DATE Returns a date based on a string and a format SUBDATE Subtracts a time/date interval from a date and then returns the date SUBTIME Subtracts a time interval from a datetime and then returns the time/datetime SYSDATE Returns the current date and time TIME Extracts the time part from a given time/datetime TIME_FORMAT Formats a time by a specified format TIME_TO_SEC Converts a time value into seconds TIMEDIFF Returns the difference between two time/datetime expressions TIMESTAMP Returns a datetime value based on a date or datetime value TO_DAYS Returns the number of days between a date and date "0000-00-00" WEEK Returns the week number for a given date WEEKDAY Returns the weekday number for a given date WEEKOFYEAR Returns the week number for a given date YEAR Returns the year part for a given date YEARWEEK Returns the year and week number for a given date
Fungsi Lanjutan MySQL
Function Description BIN Returns a binary representation of a number BINARY Converts a value to a binary string CASE Goes through conditions and return a value when the first condition is met CAST Converts a value (of any type) into a specified datatype COALESCE Returns the first non-null value in a list CONNECTION_ID Returns the unique connection ID for the current connection CONV Converts a number from one numeric base system to another CONVERT Converts a value into the specified datatype or character set CURRENT_USER Returns the user name and host name for the MySQL account that the server used to authenticate the current client DATABASE Returns the name of the current database IF Returns a value if a condition is TRUE, or another value if a condition is FALSE IFNULL Return a specified value if the expression is NULL, otherwise return the expression ISNULL Returns 1 or 0 depending on whether an expression is NULL LAST_INSERT_ID Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table NULLIF Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned SESSION_USER Returns the current MySQL user name and host name SYSTEM_USER Returns the current MySQL user name and host name USER Returns the current MySQL user name and host name VERSION Returns the current version of the MySQL database
Fungsi SQL Server
SQL Server memiliki banyak fungsi bawaan.
Referensi ini berisi string, numerik, tanggal, konversi, dan beberapa fungsi lanjutan di SQL Server.
Fungsi String SQL Server
Function Description ASCII Returns the ASCII value for the specific character CHAR Returns the character based on the ASCII code CHARINDEX Returns the position of a substring in a string CONCAT Adds two or more strings together Concat with + Adds two or more strings together CONCAT_WS Adds two or more strings together with a separator DATALENGTH Returns the number of bytes used to represent an expression DIFFERENCE Compares two SOUNDEX values, and returns an integer value FORMAT Formats a value with the specified format LEFT Extracts a number of characters from a string (starting from left) LEN Returns the length of a string LOWER Converts a string to lower-case LTRIM Removes leading spaces from a string NCHAR Returns the Unicode character based on the number code PATINDEX Returns the position of a pattern in a string QUOTENAME Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier REPLACE Replaces all occurrences of a substring within a string, with a new substring REPLICATE Repeats a string a specified number of times REVERSE Reverses a string and returns the result RIGHT Extracts a number of characters from a string (starting from right) RTRIM Removes trailing spaces from a string SOUNDEX Returns a four-character code to evaluate the similarity of two strings SPACE Returns a string of the specified number of space characters STR Returns a number as string STUFF Deletes a part of a string and then inserts another part into the string, starting at a specified position SUBSTRING Extracts some characters from a string TRANSLATE Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument. TRIM Removes leading and trailing spaces (or other specified characters) from a string UNICODE Returns the Unicode value for the first character of the input expression UPPER Converts a string to upper-case
Fungsi SQL Server Matematika / Numerik
Function Description ABS Returns the absolute value of a number ACOS Returns the arc cosine of a number ASIN Returns the arc sine of a number ATAN Returns the arc tangent of a number ATN2 Returns the arc tangent of two numbers AVG Returns the average value of an expression CEILING Returns the smallest integer value that is >= a number COUNT Returns the number of records returned by a select query COS Returns the cosine of a number COT Returns the cotangent of a number DEGREES Converts a value in radians to degrees EXP Returns e raised to the power of a specified number FLOOR Returns the largest integer value that is <= to a number LOG Returns the natural logarithm of a number, or the logarithm of a number to a specified base LOG10 Returns the natural logarithm of a number to base 10 MAX Returns the maximum value in a set of values MIN Returns the minimum value in a set of values PI Returns the value of PI POWER Returns the value of a number raised to the power of another number RADIANS Converts a degree value into radians RAND Returns a random number ROUND Rounds a number to a specified number of decimal places SIGN Returns the sign of a number SIN Returns the sine of a number SQRT Returns the square root of a number SQUARE Returns the square of a number SUM Calculates the sum of a set of values TAN Returns the tangent of a number
Fungsi Tanggal SQL Server
Function Description CURRENT_TIMESTAMP Returns the current date and time DATEADD Adds a time/date interval to a date and then returns the date DATEDIFF Returns the difference between two dates DATEFROMPARTS Returns a date from the specified parts (year, month, and day values) DATENAME Returns a specified part of a date (as string) DATEPART Returns a specified part of a date (as integer) DAY Returns the day of the month for a specified date GETDATE Returns the current database system date and time GETUTCDATE Returns the current database system UTC date and time ISDATE Checks an expression and returns 1 if it is a valid date, otherwise 0 MONTH Returns the month part for a specified date (a number from 1 to 12) SYSDATETIME Returns the date and time of the SQL Server YEAR Returns the year part for a specified date
Fungsi Lanjutan SQL Server
Function Description CAST Converts a value (of any type) into a specified datatype COALESCE Returns the first non-null value in a list CONVERT Converts a value (of any type) into a specified datatype CURRENT_USER Returns the name of the current user in the SQL Server database IIF Returns a value if a condition is TRUE, or another value if a condition is FALSE ISNULL Return a specified value if the expression is NULL, otherwise return the expression ISNUMERIC Tests whether an expression is numeric NULLIF Returns NULL if two expressions are equal SESSION_USER Returns the name of the current user in the SQL Server database SESSIONPROPERTY Returns the session settings for a specified option SYSTEM_USER Returns the login name for the current user USER_NAME Returns the database user name based on the specified id
