Post: What is wrong in this query?
02-23-2017, 03:11 PM #1
(adsbygoogle = window.adsbygoogle || []).push({}); Please have a look at the following code. It is the most basic of codes but not working. I don't know what is missing but I have been banging my head for hours and have no idea what is am doing wrong. Might be the stupidest of thing but I am unable to see it.
Can someone please tell me what is wrong in this? The SQL query is not working and is not giving any error either. It simply is not fetching any values from the table based on the conditions given.
The Database connections etc are all checked and verified.
Please have a look and point my error.

Many thanks.

    
<?php
include('core.php'Winky Winky;
$con=mysqli_connect("localhost","username","pass","db");
if (!$con)
{
die('Connection Error (' . mysqli_connect_errno() . 'Winky Winky '
. mysqli_connect_error());
}

$title=songtitle(); //taken from core.php
echo "Now playing: $title ";
echo "<br /><br />";

$sep = explode('-', $title);
echo "Album: $sep[0] <br /> Song: $sep[1] <br /> Artist: $sep[2]";
echo "<br /><br />";

$album = trim($sep[0]);
$song = trim($sep[1]);
$artist = trim($sep[2]);

$res = "SELECT `url` FROM `songs` WHERE (`album` = '" . $album . "' AND `title` = '" . $song . "' AND `artist` = '" . $artist . "'Winky Winky"; // I am pretty sure something is wrong in this line.

$sql = mysqli_query($con,$res);
$row = mysqli_fetch_array($sql,MYSQLI_ASSOC);

print $row["url"]; //No RESULT

mysqli_free_result($sql);
mysqli_close($con);
?>
02-23-2017, 04:12 PM #2
Mr Smithy x
Former Staff
Originally posted by BooYaaKaa View Post
Please have a look at the following code. It is the most basic of codes but not working. I don't know what is missing but I have been banging my head for hours and have no idea what is am doing wrong. Might be the stupidest of thing but I am unable to see it.
Can someone please tell me what is wrong in this? The SQL query is not working and is not giving any error either. It simply is not fetching any values from the table based on the conditions given.
The Database connections etc are all checked and verified.
Please have a look and point my error.

Many thanks.

    
<?php
include('core.php'Winky Winky;
$con=mysqli_connect("localhost","username","pass","db");
if (!$con)
{
die('Connection Error (' . mysqli_connect_errno() . 'Winky Winky '
. mysqli_connect_error());
}

$title=songtitle(); //taken from core.php
echo "Now playing: $title ";
echo "<br /><br />";

$sep = explode('-', $title);
echo "Album: $sep[0] <br /> Song: $sep[1] <br /> Artist: $sep[2]";
echo "<br /><br />";

$album = trim($sep[0]);
$song = trim($sep[1]);
$artist = trim($sep[2]);

$res = "SELECT `url` FROM `songs` WHERE (`album` = '" . $album . "' AND `title` = '" . $song . "' AND `artist` = '" . $artist . "'Winky Winky"; // I am pretty sure something is wrong in this line.

$sql = mysqli_query($con,$res);
$row = mysqli_fetch_array($sql,MYSQLI_ASSOC);

print $row["url"]; //No RESULT

mysqli_free_result($sql);
mysqli_close($con);
?>


Well for starters, mysqli is archaic, i wouldnt use mysqli. The way you are also making the query is unsafe because someone can easily perform an sql injection.

I would recommend using pdo.

    
<?php
include('core.php'Winky Winky;

/*
* Define SQL_HOST, SQL_DB, SQL_USER & SQL_PASS
*/
function pdo_create()
{
$con = "mysql:host=" . SQL_HOST . ";dbname=" . SQL_DB . ";charset=UTF8";
$pdo = new PDO($con, SQL_USER, SQL_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
}

$title=songtitle(); //taken from core.php
$sep = explode('-', $title);
$album = trim($sep[0]);
$song = trim($sep[1]);
$artist = trim($sep[2]);

$query = "SELECT url FROM songs s WHERE s.album = :album AND s.title = :song AND s.artist = :artist";

$pdo = pdo_create();
if(!$pdo){
die("Error");
}
$stmt = $pdo->prepare($query);
$stmt->bindParam(":album", $album);
$stmt->bindParam(":song", $song);
$stmt->bindParam(":artist", $artist);
if($stmt->execute()){
$url = $stmt->fetchColumn(0);
$stmt->closeCursor();
}else{
//error
}


?>
02-23-2017, 05:50 PM #3
Originally posted by Mr
Well for starters, mysqli is archaic, i wouldnt use mysqli. The way you are also making the query is unsafe because someone can easily perform an sql injection.

I would recommend using pdo.

    
<?php
include('core.php'Winky Winky;

/*
* Define SQL_HOST, SQL_DB, SQL_USER & SQL_PASS
*/
function pdo_create()
{
$con = "mysql:host=" . SQL_HOST . ";dbname=" . SQL_DB . ";charset=UTF8";
$pdo = new PDO($con, SQL_USER, SQL_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
}

$title=songtitle(); //taken from core.php
$sep = explode('-', $title);
$album = trim($sep[0]);
$song = trim($sep[1]);
$artist = trim($sep[2]);

$query = "SELECT url FROM songs s WHERE s.album = :album AND s.title = :song AND s.artist = :artist";

$pdo = pdo_create();
if(!$pdo){
die("Error");
}
$stmt = $pdo->prepare($query);
$stmt->bindParam(":album", $album);
$stmt->bindParam(":song", $song);
$stmt->bindParam(":artist", $artist);
if($stmt->execute()){
$url = $stmt->fetchColumn(0);
$stmt->closeCursor();
}else{
//error
}


?>


Thanks for the tip. Let me try using PDO and will post the results here.
02-23-2017, 05:56 PM #4
EDIT: Just shows a blank page. Sal

Also discovered after a bit of trial and error that if the variables have any special characters like "&, comma and brackets", the query is not working, also tried filtering variables using mysqli_real_escape_string() but no help.
Last edited by BooYaaKaa ; 02-23-2017 at 06:03 PM.
04-30-2017, 03:53 PM #5

Originally posted by BooYaaKaa View Post
Please have a look at the following code. It is the most basic of codes but not working. I don't know what is missing but I have been banging my head for hours and have no idea what is am doing wrong. Might be the stupidest of thing but I am unable to see it.
Can someone please tell me what is wrong in this? The SQL query is not working and is not giving any error either. It simply is not fetching any values from the table based on the conditions given.
The Database connections etc are all checked and verified.
Please have a look and point my error.

Many thanks.

    
<?php
include('core.php'Winky Winky;
$con=mysqli_connect("localhost","username","pass","db");
if (!$con)
{
die('Connection Error (' . mysqli_connect_errno() . 'Winky Winky '
. mysqli_connect_error());
}

$title=songtitle(); //taken from core.php
echo "Now playing: $title ";
echo "<br /><br />";

$sep = explode('-', $title);
echo "Album: $sep[0] <br /> Song: $sep[1] <br /> Artist: $sep[2]";
echo "<br /><br />";

$album = trim($sep[0]);
$song = trim($sep[1]);
$artist = trim($sep[2]);

$res = "SELECT `url` FROM `songs` WHERE (`album` = '" . $album . "' AND `title` = '" . $song . "' AND `artist` = '" . $artist . "'Winky Winky"; // I am pretty sure something is wrong in this line.

$sql = mysqli_query($con,$res);
$row = mysqli_fetch_array($sql,MYSQLI_ASSOC);

print $row["url"]; //No RESULT

mysqli_free_result($sql);
mysqli_close($con);
?>



I would recommend what smithy has said. Using PDO since Mysqli will ost likely be depreciated in the near future. You could however try the following and see if it solves the issue and also write querys depending on your MySql Engine Example InnoDB or Myisam anyways I doubt it matters but here is what I would do if i was in your position.

    

<?php
include('core.php'Winky Winky;
$con = new mysqli("localhost","username","pass","db");
if(!$con)
{
echo 'Could not connect to the database!'; // Use this if you are releasing your website publicly I would recommend lol
die();
}

$title=songtitle(); //taken from core.php
echo 'Now playing: ' . $title;
echo '<br /><br />';

$sep = explode('-', $title);
echo 'Album: ' . $sep[0] . ' <br /> Song: ' . $sep[1] . '<br /> Artist: ' . $sep[2] . ';
echo "<br /><br />";

$album = trim($sep[0]);
$song = trim($sep[1]);
$artist = trim($sep[2]);

$res = "SELECT `url` FROM songs WHERE (`album` = '" . $album . "' AND `title` = '" . $song . "' AND `artist` = '" . $artist . "'Winky Winky"; // I am pretty sure something is wrong in this line.

$sql = $con->query($con,$res);
$row = $con->fetch_array($sql, MYSQLI_ASSOC);

print $row["url"]; //No RESULT

$mysqli_stmt->free_result($sql);
mysqli_close($con);
?>

The following user thanked Program for this useful post:

BooYaaKaa
05-07-2017, 01:01 AM #6
Specter
Pro Memer
Originally posted by BooYaaKaa View Post
EDIT: Just shows a blank page. Sal

Also discovered after a bit of trial and error that if the variables have any special characters like "&, comma and brackets", the query is not working, also tried filtering variables using mysqli_real_escape_string() but no help.


The blank page is because the PHP interpreter hit an error, to debug what the error is you should check /var/logs/apache2/error.log if you're using a linux OS, and/or use the error_reporting PHP function to allow the errors to be printed to the web page directly.

    error_reporting(E_ALL);


Originally posted by Program View Post




I would recommend what smithy has said. Using PDO since Mysqli will ost likely be depreciated in the near future. You could however try the following and see if it solves the issue and also write querys depending on your MySql Engine Example InnoDB or Myisam anyways I doubt it matters but here is what I would do if i was in your position.

    

<?php
include('core.php'Winky Winky;
$con = new mysqli("localhost","username","pass","db");
if(!$con)
{
echo 'Could not connect to the database!'; // Use this if you are releasing your website publicly I would recommend lol
die();
}

$title=songtitle(); //taken from core.php
echo 'Now playing: ' . $title;
echo '<br /><br />';

$sep = explode('-', $title);
echo 'Album: ' . $sep[0] . ' <br /> Song: ' . $sep[1] . '<br /> Artist: ' . $sep[2] . ';
echo "<br /><br />";

$album = trim($sep[0]);
$song = trim($sep[1]);
$artist = trim($sep[2]);

$res = "SELECT `url` FROM songs WHERE (`album` = '" . $album . "' AND `title` = '" . $song . "' AND `artist` = '" . $artist . "'Winky Winky"; // I am pretty sure something is wrong in this line.

$sql = $con->query($con,$res);
$row = $con->fetch_array($sql, MYSQLI_ASSOC);

print $row["url"]; //No RESULT

$mysqli_stmt->free_result($sql);
mysqli_close($con);
?>



That still isn't really safe, what introduces potential vulnerabilities is constructing the parameters and the query all in one string which you're still doing, this also applies even if you're using prepared statements. In both MySQLi (not recommended) and PDO one should use the bind_param() and bindParam() functions respectively for inserting parameters into statements.

For MySQLi specifically:
    
$res = "SELECT `url` FROM 'songs' WHERE (`album` =? AND `title` =? AND `artist` =?)";

if($stmt = $con->prepare($res))
{
$stmt->bind_param("sss", $album, $title, $artist);
$stmt->execute();

$result = $stmt->get_result();
$row = $result->fetch_array(MYSQLI_ASSOC);

// Do stuff here
}

The following user thanked Specter for this useful post:

BooYaaKaa
07-21-2017, 10:18 PM #7
Originally posted by Specter View Post
The blank page is because the PHP interpreter hit an error, to debug what the error is you should check /var/logs/apache2/error.log if you're using a linux OS, and/or use the error_reporting PHP function to allow the errors to be printed to the web page directly.

    error_reporting(E_ALL);




That still isn't really safe, what introduces potential vulnerabilities is constructing the parameters and the query all in one string which you're still doing, this also applies even if you're using prepared statements. In both MySQLi (not recommended) and PDO one should use the bind_param() and bindParam() functions respectively for inserting parameters into statements.

For MySQLi specifically:
    
$res = "SELECT `url` FROM 'songs' WHERE (`album` =? AND `title` =? AND `artist` =?)";

if($stmt = $con->prepare($res))
{
$stmt->bind_param("sss", $album, $title, $artist);
$stmt->execute();

$result = $stmt->get_result();
$row = $result->fetch_array(MYSQLI_ASSOC);

// Do stuff here
}


Are you going to close the thread now ?
07-22-2017, 06:21 AM #8
Yes, Mods close the thread please.

Copyright © 2024, NextGenUpdate.
All Rights Reserved.

Gray NextGenUpdate Logo