Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

- - - - -

PHP 5: MySQLi Prepared Statements

mysqli bind_param mysql prepared statement

21 replies to this topic

#13 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 23 June 2009 - 04:47 AM

Yes, there is even the equivalent mysqli_real_escape_string function that you should use.
  • 0

#14 me999

me999

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 20 January 2011 - 05:26 AM

Nice tutorial. Always nice to be guided through the code. I struggled using prepared statements to start with and though they were cumbersome.

I am now using this wrapper class for MySQLi, which makes the prepared statement and fetches the results for you: MySQLi Class - PHP Development Project - APL Web

Check it out, you might find it useful.
  • 0

#15 monmohnish

monmohnish

    CC Lurker

  • Just Joined
  • Pip
  • 2 posts
  • Programming Language:C
  • Learning:C

Posted 09 August 2012 - 03:55 AM

Write a stored procedure and call it thru php using keyword "Call procedure_name();"
  • 0

#16 AsitMajhi

AsitMajhi

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 22 December 2012 - 10:00 PM

NIce
  • 0

#17 DavidAshby

DavidAshby

    CC Lurker

  • New Member
  • Pip
  • 6 posts
  • Location:UK
  • Learning:PHP

Posted 18 July 2013 - 03:17 PM

 

Yes, there is even the equivalent mysqli_real_escape_string function that you should use.

 

Would you use this in conjunction with php filters or could you just use the filters to sanitize input?

 

For example by using some of the following

<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Php Filters</title>
</head>

<body>
<p>By entering the following php you will get a list of the filters available to sanitize data.</p>
<p><img src="images/phpcode4Filters.png" width="203" height="89" alt="Php code you need to enter to get the list of filters that can be used to sanitize data"></p>
<p>You will get the following list</p>

</body>
</html>

<?php
	echo '<pre>';
	print_r(filter_list());
	?>
    <br><hr><br>
    <?php
	// function to validate integer
	
	$varn = "15";
	
	if(filter_var($varn,FILTER_VALIDATE_INT))
	{
		echo $varn . ' is an integer';
	}
	else
	{
		echo $varn . ' is not an integer';
	}
	?>
	<br><hr><br>
	<?php
	$vare = "1 Hello World!";
	
	if(filter_var($vare,FILTER_VALIDATE_INT))
	{
		echo $vare . ' is an integer';
	}
	else
	{
		echo $vare . ' is not an integer';
	}
	?>
    <br><hr><br>
    <?php
	/*
	This example shows that if the $var is outsaide the range for say a age 18 or over site
	that the user would not gain entry due to the min range
	*/
	
	$var1 = "12";
	$min_var = "18";
	$exp = array("options" => array("min_range"=> 18, "max_range"=> 120));
	
	
	if(filter_var($var1,FILTER_VALIDATE_INT,$exp))
	{
		echo 'You are over 18';
	}
	else
	{
		echo '<p>You are only ' . $var1 . ' years old, you are not old enough to go on to our website! <br><b>You need to be <u>' . $min_var . '</u><b></p>';
	}

?>

<br><hr><br>
    <?php
	
		// boolean 
		$varbool = "True";
		if(filter_var($varbool,FILTER_VALIDATE_BOOLEAN))
		{
			echo 'This is a valid true boolean';
		}
		else
		{
			echo 'This is not a true boolean';
		}
	
	?>
	<br><hr><br>
    <?php
	
		// boolean 
		$varbool = "0";
		if(filter_var($varbool,FILTER_VALIDATE_BOOLEAN))
		{
			echo 'This is a valid true boolean';
		}
		else
		{
			echo $varbool . ' is not a true boolean';
		}
	
	?>
	<br><hr><br>
    <?php
	
		// function to validate float values
		
		$varfloat = "15";
		
		if(filter_var($varfloat, FILTER_VALIDATE_FLOAT))
		{
			echo 'This is true, ' . $varfloat . ' is a float.  Remember floats can have decimal points in or be whole numbers.';
		}
		else
		{
			echo 'This is false, it is not a float';
		}
	
	?>
	<br><hr><br>
    <?php
	
		// function to validate float values
		
		$varfloat = "15.8";
		
		if(filter_var($varfloat, FILTER_VALIDATE_FLOAT))
		{
			echo 'This is true, ' . $varfloat . ' is a float.  **Remember** floats can have decimal points in.';
		}
		else
		{
			echo 'This is false, it is not a float';
		}
	
	?>
	<br><hr><br>
    <?php
		$letter = "p";
		$varexp = "windsor";
		$exp1 = array("options" => array("regexp" => "/^t(.*)/"));
		
		if(filter_var($varexp,FILTER_VALIDATE_REGEXP,$exp1))
		{
			echo $varexp . ' contains the letter <sup><font color="green">' . $letter . '</font></sup>';
		}
		else
		{
			echo $varexp . ' does not contain a <sup> <font color="green">' . $letter . '</font></sup> ';
		}
		
	?>
	<br><hr><br>
    <?php
		$letter = "t";
		$varexp = "thetutlage";
		$exp1 = array("options" => array("regexp" => "/^t(.*)/"));
		
		if(filter_var($varexp,FILTER_VALIDATE_REGEXP,$exp1))
		{
			echo $varexp . ' contains the letter <sup><font color="green">' . $letter . '</font></sup>';
		}
		else
		{
			echo $varexp . ' does not contain a <sup> <font color="green">' . $letter . '</font></sup> ';
		}
		
	?>
	<br><hr><br>
    
    <?php
		// validating a URL
		$varurl = "http://www.ba.co.uk";
		
		if(filter_var($varurl,FILTER_VALIDATE_URL))
		{
			echo $varurl . ' is a valid URL';
		}
		else
		{
			echo $varurl . ' is not a valid URL, please re-enter.';
		}
	
	?>
	<br><hr><br>
    
    <?php
		// validating a URL
		$varurl = "ftp://www.ba.co.uk";
		
		if(filter_var($varurl,FILTER_VALIDATE_URL))
		{
			echo $varurl . ' is a valid URL';
		}
		else
		{
			echo $varurl . ' is not a valid URL, please re-enter.';
		}
	
	?>
	<br><hr><br>
    
    <?php
		// validating a URL
		$varurl = "https://www.ba.co.uk";
		
		if(filter_var($varurl,FILTER_VALIDATE_URL))
		{
			echo $varurl . ' is a valid URL';
		}
		else
		{
			echo $varurl . ' is not a valid URL, please re-enter.';
		}
	
	?>
	<br><hr><br>
    
    <?php
		// validating a URL
		$varurl = "www.ba.co.uk";
		
		if(filter_var($varurl,FILTER_VALIDATE_URL))
		{
			echo $varurl . ' is a valid URL';
		}
		else
		{
			echo $varurl . ' is not a valid URL, please re-enter.';
		}
	
	?>
	
    <br><hr><br>
    
    <?php
		// validating email address
		$varemail = "db@gmail.com";
		
		if(filter_var($varemail,FILTER_VALIDATE_EMAIL))
		{
			echo $varemail . ' seems to be a valid email address';
		}
		else
		{
			echo $varemail . ' is not a valid email address, please re-enter.';
		}
	
	?>
	<br><hr><br>
    
    <?php
		// validating email address
		$varemail = "db.com";
		
		if(filter_var($varemail,FILTER_VALIDATE_EMAIL))
		{
			echo $varemail . ' seems to be a valid email address';
		}
		else
		{
			echo $varemail . ' is not a valid email address, please re-enter.';
		}
	
	?>
	<br><hr><br>
    <?php
		// validate an ip address
		
		// supports 2 ip address formats - ipv4 and ipv6
		
		$varip = "90.202.1.97";
		if(filter_var($varip,FILTER_VALIDATE_IP))
		{
			echo $varip . ' seems to be the right ip address';
		}
		else
		{
			echo $varip . ' indicates your not using a correct ip address';
		}
	?>
	<br><hr><br>
    <?php
		// validate an ip address - can also validate a MAC address
		
		echo '<p>supports 2 ip address formats - ipv4 and ipv6<br><br>';
		
		
		$varip = "202.1.97";
		if(filter_var($varip,FILTER_VALIDATE_IP))
		{
			echo $varip . ' seems to be the right ip address';
		}
		else
		{
			echo $varip . ' indicates your not using a correct ip address</p>';
		}
	?>
	<br><hr><br>
    <?php
		// sanitize data - 
		
		$varsanitize = "<p> This is a paragraph </p>";
		
		$after_sanitization = filter_var($varsanitize,FILTER_SANITIZE_STRING);
		
		echo 'Before sanitization' . $varsanitize;
		echo '<br><br>' . 'After sanitization ' . $after_sanitization;
		
	?>
	<br><hr><br>
    <?php
	
		// this is useful if you have a space in a url
	
		$varsent = "This is encoded";
		
		$after_sanitization2 = filter_var($varsent,FILTER_SANITIZE_ENCODED);
		echo '<p>Before encoding: -> ' . $varsent;
		echo '<br>';
		echo 'After encoding: -> ' . $after_sanitization2 . '</p>';
		
	?>
	<br><hr><br>
    <?php echo 'Lookup special chars'; ?>
    <br><hr><br>
    <?php
		
		$varsent = "<p> This is a p</p>";
		
		$after_sanitization3 = filter_var($varsent,FILTER_SANITIZE_SPECIAL_CHARS);
		echo $after_sanitization3;
		
	?>
	
     <br><hr><br>
    <?php
		// to remove tags like <script>
		echo 'This will automatically remove script tags and content between them.<br>';
		echo 'however p tags will be removed but the content within them will be visible.';
		$varscript1 = "<p> This is a js tag</p>";
		$varscript2 = "<script> This is a js tag</script>";
		$after_sanitization4 = filter_var($varscript1,FILTER_UNSAFE_RAW);
		$after_sanitization5 = filter_var($varscript2,FILTER_UNSAFE_RAW);
		echo $after_sanitization4;
		echo $after_sanitization5;
	?>
	 <br><hr><br>
    <?php
		// sanitize email address inputs
		
		$varemail2 = "support/@thetutlage.com";
		$after_sanitization6 = filter_var($varemail2,FILTER_SANITIZE_EMAIL);
		
		echo $after_sanitization6;
	?>
	
    <br><hr><br>
    <?php
		//sanitize url
	?>
	<br><hr><br>
    <?php
		// strips everything out but the numbers
		$varsannum = "This is 12";
		
		$after_sanitization7 = filter_var($varsannum,FILTER_SANITIZE_NUMBER_INT);
		echo $after_sanitization7;
	?>
	<br><hr><br>
    <?php
		// sanitize a float
		$varsfloat = "This is 12.17";
		
		$after_sanitization8 = filter_var($varsfloat,FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION);
		echo $after_sanitization8;
	?>
	<br><hr><br>
    <?php
		$varmagic = "This is john's playground";
		$after_sanitization9 = filter_var($varmagic,FILTER_SANITIZE_MAGIC_QUOTES);
		echo $after_sanitization9;
	?>
	<br><hr><br>
    <?php
		function MatchExp($value){
			if($value >= 1){
				echo "This is something we can work with";
			} else {
				echo 'Enter a number!';
			}
		}
		
		$varnumber = "5";
		$after_sanitization10 = filter_var($varnumber,FILTER_CALLBACK,array("options" => "MatchExp"));
		echo $after_sanitization10;
	?>
	
    <br><hr><br>
    <?php
	?>

Attached Thumbnails

  • phpcode4Filters.png
  • PhpFilters.png

Edited by DavidAshby, 18 July 2013 - 03:35 PM.

  • 0

#18 JasonKnight

JasonKnight

    CC Addict

  • Senior Member
  • PipPipPipPipPip
  • 312 posts
  • Location:Keene, NH
  • Programming Language:C, C++, JavaScript, Delphi/Object Pascal, Pascal, Assembly, Others

Posted 19 July 2013 - 08:52 AM

&amp;nbsp;

Yes, there is even the equivalent mysqli_real_escape_string function that you should use.

No... you should NOT be using that nonsense. I'm not even sure why the ** they felt the need to add that garbage to mysqli unless like the stupid malfing 'function equivalents' it's there to handhold the people who can't wrap their heads around objects or prepared queries.

The latter being what one SHOULD use instead of manually adding together query strings with the extra stupid time-wasting real-escape-string functions. Skip that idiocy entirely and use prepared queries. That's what they are FOR!

Edited by JasonKnight, 19 July 2013 - 08:54 AM.

  • 0
The only thing about Dreamweaver that can be considered professional grade tools are the people promoting it's use.

#19 CMR

CMR

    CC Regular

  • Member
  • PipPipPip
  • 30 posts
  • Programming Language:Java, PHP, (Visual) Basic, JavaScript, Visual Basic .NET
  • Learning:Java, PHP

Posted 30 September 2013 - 05:52 PM

This is a nice tutorial. Can I ask if there are some drawbacks or disadvantages when using prepared statements? And prepared statements are really secure?


  • 0

Constant changing == Technology


#20 robisok

robisok

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 26 March 2014 - 09:42 AM

Hi great tutorial, but how would i be able to do a "between" statement of a variable being read in from a form, i have this problem where i want to build a matchmaking system based on weight, so i have a table called users with fields age, weight and experience, i want to create a script that queries the table for the entered weight in a range that includes 1 above and one below, then echoes this range along with the users details, so in my form ive created values for the weight classes (dropdown menu)e.g. heavyweight =1 middleweight =2 and so on, so when the query is done it is looking for a number but in a range.This is how i have interpreted your above script..

<?php$mysqli = new mysqli("localhost", "root", "", "project");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if ($stmt = $mysqli->prepare("SELECT * FROM users WHERE weight BETWEEN ? AND ?")){






$stmt->bind_param('ii',$weight1 ,$weight2);




$weight1 = "weight + 1";
$weight2 = "weight - 1";


$stmt->execute();




echo "{$weight1}";
echo "{$weight2}";


$stmt->close();
}
else {


printf("Prepared Statement Error: %s\n", $mysqli->error);
}
?>
 
It basically echoes the string weight+1 weight-1 which is useless data, any ideas. Thank you in advance...

Edited by Roger, 26 March 2014 - 10:37 AM.
added codetags

  • 0

#21 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 26 March 2014 - 12:26 PM

it's not working like that. 

if weight is a number, you could do

SELECT * FROM table WHERE weight BETWEEN 70 AND 80

and then you would get all results having the weight from 70 to 80... which means your variables $weight1 and $weight2 should be numbers, not strings.

 

also, this code you provide just runs the query, it doesn't fetch any results. Your next step would be to read out the results from the $stmt and echo that thing instead.

it echoes what you have assigned to the variables, which is "weight + 1", nothing else

 

Additionally I note you do a very complicated echoing...  echo "{$weight1}"; could also be written echo $weight1; which is the very same thing.


  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#22 Dewlance

Dewlance

    CC Newcomer

  • Member
  • PipPip
  • 10 posts

Posted 18 April 2017 - 08:42 PM

Thank you for your tutorial, I am new to PHP and learning it from few days.

 

 

Can you tell me about this function? How we can use it in different ways?

$stmt->execute();


  • 0