0

I am sorry if this has been asked before but I'm not understanding most of the stuff I've found using google. I'm just learning how to do this all so please bear with me.

I've created a working "INSERT" php script to insert data into a mysql 5.x database, it works without problem, however the issue i AM having is if the user puts a word with a ' or " into the fields the script spits back a "Error inserting new record" at the user. I need to know how to make the script automatically replace the ' with a \' before it tries to insert the information to a database.

What I have at the moment is....

<?php

if (isset($_POST['submitted'])) {

include('../connect/connect-mysql.php');

$Colorist = $_POST['Colorist'];
$Active = $_POST['Active'];

$sqlinsert = "INSERT INTO colorist (Colorist, Active) VALUES ('$Colorist', '$Active')";

if (!mysqli_query($dbcon, $sqlinsert)) {
    die('error inserting new record');
    }//end of nested if statement
$newrecord = "New record added";

} //end of main if


?>
<html>
<head>
</head>
<body>
<form method="post" action="insertcolorist.php">
<input type="hidden" name="submitted" value="true" />
<fieldset>
<legend>New Colorist Data</legend>
<table border="1" width="100%" style="border-collapse: collapse">
<tr><th colspan="2"><font face="Verdana" size="2">Colorist Data</font></th></tr>
<tr><th><font face="Verdana" size="1"><label>Colorist: </label></font></th><td><font size="1" face="Verdana"><input type="text" size="150" name="Colorist" /></font></td></tr>
<tr><th><font face="Verdana" size="1"><label>Is the Colorist Active: </label></font></th><td><font size="1" face="Verdana"><select size="1" name="Active"><option value="">Select...</option><option value="Yes">Yes</option><option value="No">No</option></option></select></font></td></tr>
</table>
</fieldset>
<br>
<input type="submit" value="add new colorist" />
</form>
<?php
echo $newrecord // New record added statement added at the top
?>
chopper
  • 6,649
  • 7
  • 36
  • 53
JoJo
  • 43
  • 6
  • possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Lawrence Cherone Mar 12 '14 at 21:13
  • also the font tag has been deprecated for an age... – Lawrence Cherone Mar 12 '14 at 21:15
  • no i dont want to prevent something, this is for my own use (and a couple friends who are helping me) to populate my databse easier... I dont want to PREVENT an entry, I want to make it so that ' or " dont PREVENT me from doing it cause I will also use this information on another insert script i have that includes inserting blocks of text with speech quotes in it – JoJo Mar 13 '14 at 05:10
  • the `'` and `"` characters are used in sql, your basically SQL injection yourself, which is BREAKING your query. PREVENTing sql injections will FIX the issue your having. – Lawrence Cherone Mar 13 '14 at 13:00

1 Answers1

0

There's a mysqli function for cleaning input string. It's as easy as use 

$Colorist = mysqli_real_escape_string($_POST['Colorist']);
$Active = mysqli_real_escape_string($_POST['Active']);

This should be enough for the most common problems of this type

Olvathar
  • 551
  • 3
  • 10
  • and this would go where i have $Colorist = $_POST['Colorist']; ? or is it on a separate area, cause I tried it on that line and it didn't work – JoJo Mar 13 '14 at 04:11
  • My fault, sorry, forgot an argument: `$Colorist = mysqli_real_escape_string($dbcon, $_POST['Colorist']); $Active = mysqli_real_escape_string($dbcon, $_POST['Active']);` And yes, it would go on the same line where you now set $Colorist – Olvathar Mar 13 '14 at 07:36
  • ok this worked perfectly for THIS script, but I tried to implement it on another insert script of the exact same type ( only difference is the # of fields and where it inputs the data, it created a empty record. `include('../connect/connect-mysql.php'); $Power = mysqli_real_escape_string($dbcon, ['Power']); $sqlinsert = "INSERT INTO powercat (Power) VALUES ('$Power')";` – JoJo Mar 13 '14 at 07:55
  • Does this code `['Power']` not fail you? won't you need `$_POST['Power']`? – Olvathar Mar 13 '14 at 08:01
  • AHA thats what messed up, thanks @_@ dunno how I lost that... it musta got missed int eh find/replace.. i greatly appreciate your help – JoJo Mar 13 '14 at 08:10