This question already has an answer here:
- How can I prevent SQL injection in PHP? 28 answers
I am still new to php and MySQL as far as development is concerned. I would love some advice to make sure I am doing everything correctly. I have one page that I believe is converted the correct way and one page that I need to convert.
Here is the one that I have setup is this correct?
<?php $title = 'SEARCH'; $page = '';include 'includes/header.php';?>
<body>
<?php include 'includes/nav.php'; ?>
<?php
$q = $_GET['q'];
// CONNECT TO THE DATABASE
$DB_NAME = 'code_storage';
$DB_HOST = 'localhost';
$DB_USER = 'user';
$DB_PASS = 'pass';
try {
$dbcon = new PDO("mysql:host=$DB_HOST;dbname=$DB_NAME", $DB_USER, $DB_PASS);
//echo 'Connected to database';
$sql = <<<SQL
SELECT *
FROM snippets
WHERE CODE_NAME LIKE '%$q%' OR
CODE_DESC LIKE '%$q%' OR
CODE_TAGS LIKE '%$q%' OR
CODE_USAGE LIKE '%$q%'
SQL;
echo '<div class="row">';
echo '<div class="panel">';
printf("Your search for <b>$q</b> returned %d records.
", $dbcon->query($sql)->rowCount());
echo '</div>';
echo '</div>';
foreach ($dbcon->query($sql) as $row) {
//print $row['CODE_NAME'] . '<br/>' . "
";
echo ' <div class="row">' . "
";
echo ' <div class="large-12 columns">' . "
";
echo ' <b><a href="results.php?id=' . $row['_ID'] . '">' . $row['CODE_NAME'] . '</a></b><br/><br/>' . "
";
echo ' </div>' . "
";
echo ' </div>' . '<br/><br/>' . "
";
}
$dbcon = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
<?php include 'includes/footer.php';?>
And here is the one I still need to convert this is the once I could really use some help with
<?php
// CONNECT TO THE DATABASE
$DB_NAME = 'code_storage';
$DB_HOST = 'localhost';
$DB_USER = 'user';
$DB_PASS = 'pass';
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if (mysqli_connect_errno()) {
printf("Connect failed: %s
", mysqli_connect_error());
exit();
}
// Fix for the ' and "
$_POST['name'] = $mysqli->real_escape_string($_POST['name']);
$_POST['desc'] = $mysqli->real_escape_string($_POST['desc']);
$_POST['usage'] = $mysqli->real_escape_string($_POST['usage']);
$_POST['code'] = $mysqli->real_escape_string($_POST['code']);
$_POST['tags'] = $mysqli->real_escape_string($_POST['tags']);
$sql = <<<SQL
INSERT
INTO snippets
(CODE_NAME,CODE_DESC,CODE_USAGE,CODE_SYNTAX,CODE_TAGS)
VALUES
('$_POST[name]', '$_POST[desc]', '$_POST[usage]', '$_POST[code]', '$_POST[tags]');
SQL;
if(!$result = $mysqli->query($sql)){
echo '<br/><br/><br/><br/>' . "
";
echo '<div class="row">' . "
";
echo ' <div class="large-12 columns">' . "
";
echo ' <div data-alert class="alert-box alert">' . "
";
echo ' There was an error' . "
";
echo ' <a href="../site/upload.php" class="close">×</a>' . "
";
echo ' </div>' . "
";
echo ' </div>' . "
";
echo '</div>' . "
";
echo '<br/><br/><br/><br/>' . "
";
echo '<div class="row">' . "
";
echo ' <div class="large-12 columns">' . "
";
die('There was an error with the code [' . $mysqli->error . ']');
echo ' </div>' . "
";
echo '</div>' . "
";
}
echo '<br/><br/><br/><br/>' . "
";
echo '<div class="row">' . "
";
echo ' <div class="large-12 columns">' . "
";
echo ' <div data-alert class="alert-box success">' . "
";
echo ' Code Successfully Added' . "
";
echo ' <a href="../site/" class="close">×</a>' . "
";
echo ' </div>' . "
";
echo ' </div>' . "
";
echo '</div>' . "
";
echo '<br/><br/><br/><br/>' . "
";
*/
?>
*EDIT* Here is what I have so far but it doesn't seem to be returning any records any ideas why?
$q = $_GET['q'];
$DB_NAME = 'code_storage';
$DB_HOST = 'localhost';
$DB_USER = 'user';
$DB_PASS = 'pass';
$dsn = "mysql:host=$DB_HOST;dbname=$DB_NAME";
$db = new PDO($dsn, $DB_USER, $DB_PASS);
$query = "SELECT * FROM `SNIPPETS` WHERE `CODE_NAME` LIKE :name OR `CODE_DESC` LIKE :name OR `CODE_TAGS` LIKE :name OR `CODE_USAGE` LIKE :name";
$prep = $db->prepare($query);
$prep->execute(array(":name" => "%" . $q . "%"));
echo '<div class="row">';
echo '<div class="panel">';
printf("Your search for <b>$q</b> returned %d records.
", $prep->rowCount());
echo '</div>';
echo '</div>';
while ($row = $prep->fetch()) {
echo ' <div class="row">' . "
";
echo ' <div class="large-12 columns">' . "
";
echo $row['CODE_NAME'] . '<br/><br/>' . "
";
echo $row['CODE_DESC'] . '<br/><br/>' . "
";
echo $row['CODE_USAGE']. '<br/><br/>' . "
";
echo ' </div>' . "
";
echo ' </div>' . '<br/><br/>' . "
";
}
$db = null;
I have found out that I am getting this error
Connection failed: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
</div>
