Preventing SQL Injections

Question

So I'm pretty new to SQL so I'm not too familiar with how to prevent any SQL injections.

I have 1 main form that's being used as a poll and it currently only has 1 text field. At the moment I think I'm safe from the generic '); DROP TABLE table; -- exploit but there are more I'm not too sure about.

Below are my insert and update lines. $want is the text field I mentioned.

INSERT INTO polls (ip, continent, hours, timespent, f_champ, l_champ, o_champ, u_champ, b_champ, w_champ, want, story, happy, 4star, 3star, 2star, 1star) VALUES ($ip, '$continent', $hours, '$timespent', '$f_champ', '$l_champ', '$o_champ', '$u_champ', '$b_champ', '$w_champ', CAP_FIRST('$want'), '$story', '$happy', $_4star, $_3star, $_2star, $_1star)

UPDATE polls SET continent = '$continent', hours = $hours, timespent = '$timespent', f_champ = '$f_champ', l_champ = '$l_champ', o_champ = '$o_champ', u_champ = '$u_champ', b_champ = '$b_champ', w_champ = '$w_champ', want = CAP_FIRST('$want'), story = '$story', happy = '$happy', 4star = $_4star, 3star = $_3star, 2star = $_2star, 1star = $_1star WHERE ip = $ip

I'm currently using PHP and MySQLi (procedural) inserting into a phpMyAdmin database.

Can anyone provide any tips to stop potential injections. Thanks.

Is there any reason that How can I prevent SQL-injection in PHP? didn't suit your needs? — MonkeyZeus, Feb 11 '15 at 19:27
See what happens in your UPDATE statement if someone manages to get Africa'-- into $continent ? Use prepared statements, and bind variables. If, for some reason, you absolutely can't use bindings, sanitize your input first. Allowing nothing but alphanumerics helps in most cases. — Guntram Blohm, Feb 11 '15 at 21:00

score 6 · Accepted Answer · edited Feb 11 '15 at 21:17

The proper way to prevent SQL injections is to use Prepared Statements.

Here is an abbreviated example taken from the PHP documentation:

$stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)");
$stmt->bind_param("i", $id);
$stmt->execute();

Instead of literally substituting the value of a variable in your query, which will always be vulnerable to injection by a creative attacker, you use a wildcard - "?" . The variable must then be bound to the wildcard.

Obviously this is a much simplified example. You need to do error handling, and often you'll have more than one variable in your statement. All of this can be found in the documentation.

The concept of prepared statements is not unique to PHP. You find it in practically all programming languages, although the implementation may differ. Familiarize yourself with the concept, because you're going to encounter and need it often.

Is there a Procedural method for Prepared statements? Objects still confuse the hell out of me in PHP. — Spedwards, Feb 11 '15 at 14:47
Yes, MySQLi provides a dual interface - both procedural and object-based. — S.L. Barth is on codidact.com, Feb 11 '15 at 14:52

score 1 · Answer 2 · answered Feb 11 '15 at 15:10

At the moment I think I'm safe from the generic '); DROP TABLE table; -- exploit but there are more I'm not too sure about.

You will be save from this most of the time, as you have to explicitly use multi_query in PHP + MySQL to allow stacked queries (multiple queries separated by a semicolon)

What you are not save from are SQL injections that do not use stacked queries. Those are for example:

in select statements: using union to combine the legitimate select statement with a second, injected select statement, thus retrieving data that should not have been retrieved (and possibly writing/reading files).
in update/delete/insert statements: error based injection using XPATH via updatexml or extractdata. Basically, you generate an error message that contains the data you want to extract from the database.

Injections are also possible in order by and limit clauses.

As @S.L. Barth said, the correct way to prevent SQL injection is to always use prepared statements for all variable data.

Preventing SQL Injections

2 Answers2

Linked