In more detail
SQL injection happens when a program builds a query by pasting user input into a string, so an input such as ' OR '1'='1 changes the query's logic. The database cannot tell which part the programmer wrote and which part the attacker typed. The fix is parameterised queries (prepared statements), which send the command and the values separately, so input is only ever treated as data. Escaping by hand is error-prone. Least-privilege database accounts limit the damage, and a web application firewall is only a backstop.
Why it matters
A single vulnerable search box or login form can expose or alter an entire database: customer records, password hashes, even the ability to delete tables or, on some systems, run commands. It has been a top web risk for decades and is still found in new code, especially where queries are assembled with string formatting. It is a kind of Vulnerability.
Example
Table users(name, pw) holds alice and bob. The code builds SELECT name FROM users WHERE name = ' + input + '. With the input x' OR '1'='1 the query becomes:
SELECT name FROM users WHERE name = 'x' OR '1'='1'
and returns both rows, alice and bob. With the input x' UNION SELECT pw FROM users -- it returns the stored passwords instead. The parameterised form SELECT name FROM users WHERE name = ? with the same text as the value returns no rows, because nothing is named that.
Common confusion
SQL injection attacks the database through the server, while Cross-site scripting (XSS) attacks other users' browsers. Both come from mixing untrusted input with code, but the fix differs: parameterise queries versus escape output.