Need help with a mysql query.

[eldan88]

Hey Guys. I am trying to run a mysql query. The code below is very basic, but is actually implemented in  a class that I have. Based on the condition on my real code $val3 $val4 are defined as both INT and will not always be defined.

 

As default I have defined them as NULL. But every time I run the query I keep getting  the following mysql error .. "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' )' at line 4"

 

Doesn't the values get omitted when defined as NULL or set as an empty string.

 

Is there a way I can prevent those values being parsed if they are NULL or ""??

 

Below is also an example how it would be implemented in a class..

 

As always any help would be highly appreciated!!

<?php 


$val1 = "val";
$val2 = "val2"; 
$val3 = "val3";
$val4 = "";
$val5 = "";


$mysqli = new mysqli("localhost", "root", "", "sandbox");

$query = "INSERT INTO test_values (
    val1, val2, val3, val4, val5 ) VALUES (

'{$val1}' , '{$val2}' , '{$val3}' , {$val4} , {$val5}
    
       


	);";


$mysqli->query($query);
echo $mysqli->error;

Here is a real world example. (I may have some syntax errors as it is a quick and dirty example)

class PostValues {
	

   public $val1 = "";
   public $val2 = "";
   public $val3 = "";
   public $val4 = "";


public function form_post_values() {
       $this->val1 = prep_mysql_values($_POST['val1']); 
	$this->val2 = prep_mysql_values($_POST['val2']); 

//Only assign the POST values if the option equal to Credit Card
  $this->payment_method = prep_mysql_values($_POST['payment_type']);
  if($_POST['payment_method'] == "Credit Card") {
 $this->val3 = prep_mysql_values($_POST['cc_numr']);
  $this->val4= prep_mysql_values($_POST['cvv']);
}

}

[gizmola]

There is nothing magic about a sql statement, so if by omitted, you mean that it will take an empty variable and turn it into the string 'null', which is what the sql statment would be happy with, then that is not going to work.

 

For example, if you start with these values:

 

$val1 = "val";
$val2 = "val2";
$val3 = "val3";
$val4 = "null";
$val5 = "null";

Then you should get past your SQL error.

 

Another solution is to put single quotes around the values, then mysql will probably convert that to a zero, and the statement will be accepted.

 

However, I feel that all of these workarounds are missing the point, because you should be using prepared statements with mysqli.

 

That would be of the general form:

 

$stmt = $mysqli->prepare(...
$stmt->bind_param('sssii', $val1, $val2, $val3, $val4, $val5);
$stmt->execute();

Interpolating strings into sql statements is not good practice with mysqli or pdo, and can easily lead to sql injections.

[eldan88]

Hey Gizmoal! I am new to using mysqli. I did a little bit of research on prepared statement and thought it was only used for select statements. In terms of SQL injection isnt functions like mysql_real_escape_string and strip_tags wrapped around the POST values is already going to prevent that?

 

A solutions also popped into my head while I was on the train... i type casted $val3 and $val4 to intval which was the reason I was getting those errors.... in the the construct method like so...

 

I tried type casing the properties it self but was getting a syntax error from my editor... not sure why.

public $val1 = "";
public $val2 = "";
public $val3 = "";
public $val4 = "";


public __construct() {
$this->$val3 = intval("");
$this->$val4 = intval("");
}

public function form_post_values() {
$this->val1 = prep_mysql_values($_POST['val1']);
    $this->val2 = prep_mysql_values($_POST['val2']);

//Only assign the POST values if the option equal to Credit Card
$this->payment_method = prep_mysql_values($_POST['payment_type']);
if($_POST['payment_method'] == "Credit Card") {
$this->val3 = prep_mysql_values($_POST['cc_numr']);
$this->val4= prep_mysql_values($_POST['cvv']);
}

[eldan88]

Hey so I have been doing reading  about prepared statements, and it explains how its faster when executing many times. Does that fall for SQL statements that involve while and for loops?? If i was to add a prepared statement in a check out page for example. The SQL statement will get executed once.. correct?

[Jacques1]

Hi,

 

 

Hey Gizmoal! I am new to using mysqli. I did a little bit of research on prepared statement and thought it was only used for select statements. In terms of SQL injection isnt functions like mysql_real_escape_string and strip_tags wrapped around the POST values is already going to prevent that?

 

strip_tags() has absolutely nothing to do with SQL. It removes HTML tags (hence the name). But since doing that is complete nonsense, it's probably best to strike this function from your memory.
 

mysql_real_escape_string() does prevent SQL injections, at least in theory. In reality, many developers fail to use it correctly, or they simply forget to use it once in a while. That's why we hear about a new SQL injection vulnerability every few days.

 

Prepared statements, on the other hand, are a much more robust solution. They completely separate the data from the query itself, which makes it impossible to manipulate the query through the input. I strongly recommend using prepared statement at all times.

 

 

 

Hey so I have been doing reading  about prepared statements, and it explains how its faster when executing many times. Does that fall for SQL statements that involve while and for loops?? If i was to add a prepared statement in a check out page for example. The SQL statement will get executed once.. correct?

 

There's a famous quote by Donald Knuth: “Premature optimization is the root of all evil.”

 

Whether or not reusing a prepared statement saves you a couple of milliseconds is completely irrelevant. Don't waste your time with this. As long as there are no performance issues, there's no need to worry about them. And if you do experience performance issues, you need to investigate the reason.

 

Most of the time, a prepared statement only gets executed once. Yes.

 

 

 

[eldan88]

Jackques1. Thanks for the reply... I guess I will have to implement it... looks like I have some work to do for tomorrow

[gizmola]

Jackques1. Thanks for the reply... I guess I will have to implement it... looks like I have some work to do for tomorrow

I agree strongly with everything Jacques1 wrote.

 

In regards to prepared statements on insert, IF you have multiple inserts to perform at one time, it is faster to do it with prepared statements, but rarely are there situations where the performance increase makes a difference. Most inserts are done one at a time, although it's always good to know that you have the option of doing a few if needed, and that those will be optimal.

 

However, as Jacques1 stated, it's simply more secure to use the prepared statements and variable binding, and that's reason enough. You also lessen complexity, because you no longer have to worry about escaping strings.