How to prevent SQL injection?

Question

I'm currently using the following insert query to update values in mysql using php:

    $UpdateQuery =  "UPDATE  `mysqldb`.`cu_data` SET            
                    `app_status` = '".$_POST['Est']."', 
                    `nacionalidad` = '".$_POST['Nac']."', 
                    `ciudad_ini` = '".$_POST['CiudadI']."', 
                    `ciudad_ent` = '".$_POST['CiudadE']."', 
                    `ciudad_dest` = '".$_POST['CiudadD']."', 
                    `ciudad_land` = '".$_POST['PEntrada']."', 
                    `uso_consr` = '".$_POST['U_Abog']."', 
                    `start` = '".$_POST['Envi']."', 
                    `t1` = '".$_POST['Cob']."', 
                    `t2` = '".$_POST['Acus']."', 
                    `t3` = '".$_POST['Invit']."', 
                    `t4` = '".$_POST['Entre']."', 
                    `t5` = '".$_POST['Recibo']."', 
                    `t6` = '".$_POST['EnvioF)']."', 
                    `t7` = '".$_POST['DatInternet']."', 
                    `t8` = '".$_POST['SoliMed']."', 
                    `t9` = '".$_POST['OrdeMe']."', 
                    `t10` = '".$_POST['SoliciPasa']."', 
                    `t11` = '".$_POST['EnvioPasas']."',
                    `t12` = '".$_POST['RecepPasa']."', 
                    `end` = '".$_POST['Landing']."', 
                    `Notas` = '".$_POST['Notas']."', 
                    `LastChange` = NOW()
                    WHERE  `cu_data`.`procid` = '".$_POST['Proceso']."' AND 
                            `cu_data`.`userid` = '$userid'
                    "; 
    $result = mysqli_query($con, $UpdateQuery);

The code is working, but now I'm worried about sql inyections (not only the bad kind, but also things like the user including a semi-colon in any of the form fields)

I was thinking using some kind of escaping using mysql_real_escape_string (as suggested in the sencond most voted answer to this question), but then I see some pitfalls in the comments (and I'm having a hard time understanding the most voted one).

any tips?

thanks in advance

Ps: At least for the last field (notas) I need the user to enter any symbols such as ($ # % ; : ,.>

Edited:

I did look at the suggested answer (sorry I had a bad like to another SO answer in the original question). So, as not to be duplicate, could I ask for a clue on on how to do the PDO thing to a update query like the one I'm showing above? (My php is from the mysql_ days!)

Edited (2): Ok, so this question has been flagged as duplicate, yet I don't agree with the selected answer (the one that shows after This question already has an answer here: as that's the one I read before asking this question. I did found another one that looks more interesting (at least to me) here: Replacing mysql_* functions with PDO and prepared statements (of course this one starts to make sense once one is aware of PHP Data Objects, aka PDO, which I wasn't

Edited (3): Well I got this to work as follow:

     $UpdateQuery1 =  "UPDATE  `mysqldb`.`cu_data` SET            
                    `app_status` = ?, 
                    `nacionalidad` = ?, 
                    `ciudad_ini` = ?, 
                    `ciudad_ent` = ?, 
                    `ciudad_dest` = ?, 
                    `ciudad_land` = ?, 
                    `uso_consr` = ?, 
                    `start` = ?, 
                    `t1` = ?, 
                    `t2` = ?, 
                    `t3` = ?, 
                    `t4` = ?, 
                    `t5` = ?, 
                    `t6` = ?, 
                    `t7` = ?, 
                    `t8` = ?, 
                    `t9` = ?, 
                    `t10` = ?, 
                    `t11` = ?,
                    `t12` = ?, 
                    `end` = ?, 
                    `Notas` = ?, 
                    `LastChange` = NOW()
                     WHERE  `cu_data`.`procid` = ? AND 
                            `cu_data`.`userid` = ?";
                   $stmt = $con->prepare($UpdateQuery1);     
       $stmt->bind_param('ssssssssssssssssssssssss',      
$_POST['Estatus'],$_POST['Nacionalidad'],$_POST['CiudadI'],$_POST['CiudadE'],$_POST['CiudadD'],
$_POST['PEntrada'],$_POST['Uso_Abog'],$_POST['Envi'],$_POST['Cobro_de_Fee'],
$_POST['Acus'],$_POST['Invit'],$_POST['Entre'],$_POST['Recibo'],$_POST['EnvioF'],
$_POST['DatInternet'],$_POST['SoliMed'],$_POST['OrdeMe'],$_POST['SoliciPasa'],
$_POST['EnvioPasa'],$_POST['RecepPasa'],$_POST['Landing'],$_POST['Notas'],
$_POST['Proceso'],$userid);


       $stmt->execute();

As a bonus, my field Notas seems to be able to hold any text, without having to escape special character

First comment - **don't** use `mysql_*()` functions. They are deprecated and support will be removed soon. Second comment: Switch to `mysqli` or `PDO`, and look at using prepared statements — , Nov 07 '13 at 03:26
-1 Because this is one of the easiest questions to find a duplicate for . (Tip: when writing a question, look at the suggested related questions. It helps if the correct spelling is used.) — user2864740, Nov 07 '13 at 03:30
Hi Mike, I use `$result = mysqli_query($con, $UpdateQuery); ` — AlvaroFG, Nov 07 '13 at 03:31
Hi @user2864740 I did check other questions (there is even a reference to one in my question). Yet, as mentioned in the original question, I'm having a hard time understanding the PDO thing — AlvaroFG, Nov 07 '13 at 03:38

Matthew Johnson · Accepted Answer · 2014-05-16T20:41:13.187

2

For best protection against injection attacks, use mysqli or PDO and prepared statements. Mysql_* functions are deprecated. Info on mysqli can be found in the documentation.

For example, a query with prepared statements looks like this:

$stmt = $mysqli->prepare('SELECT lastname FROM customers WHERE id = ?');
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt -> bind_result($lastName);
$stmt -> fetch();

The $id holds the string to be escaped, and the $lastName variable will hold the value returned from the database. This will prevent sql injection attacks.

edited May 16 '14 at 20:41

answered Nov 07 '13 at 03:26

Matthew Johnson

4,875
2
38
51

by "using mysqli" you mean something more than running this query using mysqli_query()? – AlvaroFG Nov 07 '13 at 03:46
Yes, use prepared statements as opposed to a simple mysqli_query whenever you will be using user input within the query. I'll update my answer with an example. – Matthew Johnson Nov 07 '13 at 14:24
Great, will it works the same for an Update query?, I mean, can I do something like `$stmt->bind_param('s', $_POST['Lastname']);` also can I use multiple `?` and then do multiple binding? – AlvaroFG Nov 07 '13 at 20:29
1

Absolutely. Multiple paramaters would need more characters in the first argument of the bind_param(). "s" is for strings, "i" is for integers. So if you were getting two strings and a number, it would look something like $stmt->bind_param('ssi', $_POST['firstName'], $_POST['lastName'], $_POST['age']); To loop through several rows, you would use something like while($stmt->fetch()) { echo "$lastName, $firstName"; } – Matthew Johnson Nov 07 '13 at 20:44
thank you! an answer like this is was I was expecting. – AlvaroFG Nov 08 '13 at 00:11
I got this to work, but I had to add the following: `$stmt = $con->prepare($sql);` where $con is my sql connection generated with `mysqli_connect()` – AlvaroFG Nov 08 '13 at 23:49

How to prevent SQL injection?

1 Answers1

Linked