SQL

Structured Query Language (or SQL) is a data-manipulating language for relational database management systems. It is used to define, query, update, and manage relational data. Many websites and imageboard frameworks such as Yotsuba rely on it. SQL is distinct from using ad-hoc string operations such as PHP's explode()
/foreach()
/implode()
for data manipulation.
Overview[edit | edit source]
There are many relational database systems that implement SQL, but for most website or imageboard projects the differences are minor. MySQL (and its fork MariaDB), PostgreSQL, SQLite, and Microsoft SQL Server are the most common. At the end of the day, it does not really matter which database system you pick as they all typically behave just about same and are partially interchangeable, so it's just about picking what works best for you.
- MySQL / MariaDB – Probably the most common known system. It is easy to set up and is widely supported. Basically the gold standard.
- PostgreSQL – More standards-compliant and feature-rich. Has support for "plugins" making it very flexible.
- SQLite – File-based database stored in a single .db file. It’s coal since it doesn’t handle high traffic very well because it writes everything to an single file. Good for testing or small projects though.
- Microsoft SQL Server – Microsoft’s database system. Has its own procedural language (T-SQL), lots of management/reporting tools, and heavy integration with Windows/.NET.
- MS Access ODBC – Is mainly used with Microsoft Windows + IIS + ASP servers from 1997.
There are different database access APIs as well, for writing database-agnostic code.
- ODBC (Open Database Connectivity) – a standard API for accessing databases. Any database management system that has an ODBC driver will support it. It is primarily for C, but you can get wrappers for it for C++ and Python.
- JDBC (Java Database Connectivity) – an API specifically for Java applications. Similar to ODBC.
SQL injection[edit | edit source]
![]() |
![]() ![]() You can read this for more info about the topic |
![]() |
---|
A SQL injection is a hacking technique to insert malicious SQL statements into an entry field to be executed.
Consider a simple PHP script that builds a query by concatenating a user-supplied ID:
$id = $_GET['id']; // attacker-controlled $sql = "SELECT * FROM users WHERE id = '$id'"; $result = mysqli_query($conn, $sql);
An attacker can request ?id=1' OR '1'='1
, then the SQL becomes:
SELECT * FROM users WHERE id = '1' OR '1'='1'
Similarly, one can bypass logins like so:
$username = $_POST['username']; $password = $_POST['password']; $sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
If an attacker sets password
to anything' OR '1'='1
, the WHERE
becomes always true, bypassing login.
If a website is shit enough, you could use this in raids. This has been done before on the sharty. If you don't want your site to get injected then use htmlspecialchars() or escapequotes() o algo
Example[edit | edit source]
Here are some examples in Java.
Unsafe:
package party.soyjak.example; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import jakarta.servlet.http.HttpServletRequest; import jakarta.servlet.http.HttpServletResponse; protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException { String id = req.getParameter("id"); // attacker-controlled String st = String.format("SELECT * FROM users WHERE id = '%s'", id); // string concat -> SQLi try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(st)) { // ... process result ... } catch (SQLException e) { resp.sendError(500, "DB error"); } }
Safe:
package party.soyjak.example; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.PreparedStatement; import java.sql.SQLException; import jakarta.servlet.http.HttpServletRequest; import jakarta.servlet.http.HttpServletResponse; protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException { String idParam = req.getParameter("id"); // Validate/normalize if expecting an integer int id; try { id = Integer.parseInt(idParam); } catch (NumberFormatException ex) { resp.sendError(HttpServletResponse.SC_BAD_REQUEST, "Invalid id"); return; } String st = "SELECT id, username, email FROM users WHERE id = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(st)) { ps.setInt(1, id); try (ResultSet rs = ps.executeQuery()) { if (rs.next()) { // handle row safely } else { resp.setStatus(HttpServletResponse.SC_NOT_FOUND); } } } catch (SQLException e) { // log detail server-side, but don't leak SQL/errors to user logger.error("DB error", e); resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "Server error"); } }
Thus, to prevent SQL injections:
- Use parametrized queries and prepared statements
- Validate input and whitelist specific values
- The DB user used by the app should only have necessary privileges (no
DROP
, etc.) - Avoid verbose database errors to users: log errors safely and do not display raw SQL or stack traces
- Use ORMs carefully, watch raw queries
- Use DB-specific escaping only when parametrization can't be used
- Whitelist identifiers, map user choices to allowed names (do not accept arbitrary identifiers)
- Use web application firewalls and query-logging and alerts for suspicious patterns
Peer reviewed sources [+]
| |
Fields of science [+]
Archeology ♦ Biology ♦ Virology ♦ Nutritionial science ♦ Mathematics ♦ Sociology ♦ Psychology ♦ Technology ♦ Philosophy ♦ Zoology ♦ Ajakogenesis ♦ Robotics | |
Science in praxis [+]
Fourth Industrial Revolution ♦ Communism ♦ Meds ♦ Atheism ♦ Abortion ♦ Pod ♦ Bugs ♦ Quarantine | |
Theoretical branches [+]
|