INSERT method with PHP OO error using SQL SERVER 2012

Question

This is my class connection in the database

class Conection {
    private $_host = 'ALISON\MSSQLSERVER2';
    private $_user = 'banco';
    private $_pass = '123456';
    private $_database = 'aulateste';
    private $_con;
    function __construct(){
        $this->conecta();
    }
    public function conecta() {
        $_coninfo = array("Database" =>$this->_database, "UID" =>$this->_user, "PWD" =>$this->_pass);
        $_con = sqlsrv_connect($this->_host, $_coninfo);

        if($_con) {
            echo "Conexão estabelecida";
        }else{
            echo "Conexão não estabelecida";
            die( print_r( sqlsrv_errors(), true));
            echo "<br>";
        }
    }
}

This is my method of insert :

public function cadastrar_autor($Id_autor,$Nome_autor, $Sobrenome_autor){
        $sql = "INSERT INTO tbl_autor VALUES($this->Id_autor,$this->Nome_autor,$this->Sobrenome_autor)";
        $sql_inserir = sqlsrv_query($sql,$this->conect);                                   
        if($sql_inserir > 0){
            echo"Cadastrou";
        }else{
            die( print_r( sqlsrv_errors(), true));
        }
    }

Error

An invalid Parameter was passed to sqlsrv_query

Answer 1

Your code needs some observations:

1) When registering something in the database, it is recommended to keep a key auto_increment, this way there is no need to increment the author id.

2) When you pass parameters to insert data in the BD, you should put the quotation marks, only for values (boolean, numeric, now(), null, etc... that there is no need for quotation marks). However, passing the values without a treatment before, is an unsafe way of registering data in the database, as it gives space to SQL Injection, you can use treatments like: preg_split(), preg_replace('/sua_expressao_regular/','',$data), and other "bad things". I recommend using PDO in this case, if you are working with SQL Server, it contains placeholders that avoid this type of data entry and risk see the PDO here.

Solving the question, if you’re using $this, that belongs to your class in question does not make sense to pass the data by parameter. I understand that you must have already assigned by a method set, the data "name" and "surname", since they are attributes of your class, but I would make an improvement on this, not using capital letter at the beginning of attributes and follow the standard of normalization of Psrs:

private $Nome_autor;
private $Sobrenome_autor;

public function setNome($string)
{
    $this->Nome_autor = $string;
}

public function setSobrenome($string)
{
    $this->Sobrenome_autor = $string;
}
public function cadastrar_autor()
{
    $sql = "INSERT INTO tbl_autor (nome, sobrenome) VALUES ('$this->Nome_autor','$this->Sobrenome_autor')";
   $sql_inserir = sqlsrv_query($sql,$this->conect);                                   
    if ($sql_inserir > 0) {
       echo"Cadastrou";
    } else {
      die( print_r( sqlsrv_errors(), true));
    }
}

Otherwise, it would be right to do this:

public function cadastrar_autor($nome, $sobrenome)
{
    $sql = "INSERT INTO tbl_autor (nome, sobrenome) VALUES('$nome','$sobrenome')";
    $sql_inserir = sqlsrv_query($sql,$this->conect);                                   
    if ($sql_inserir > 0) {
       echo"Cadastrou";
    } else {
    die( print_r( sqlsrv_errors(), true));
    }
}

It would also separate the connection class from its entity "Author".

Answer 2

By the question code fragments and by the comments the main problem is that the connection is not made or passed correctly to the class Autor that performs the Insert.

Other problems are:

• The class Conection does not store or return the connection.

• The order of the arguments of sqlsrv_query() is inverted;

• The code is vulnerable to sql Injection

Answer 3

public function insert($sql){
    $return = sqlsrv_query($this->_con,$sql);
    if($return > 0){
        echo"deu";
    }else{
        die( print_r( sqlsrv_errors(), true));
    }
}

public function cadastrar_autor($Id_autor,$Nome_autor, $Sobrenome_autor){
  $sql = "INSERT INTO tbl_autor(Id_autor,Nome_autor,Sobrenome_autor)VALUES('$Id_autor','$Nome_autor','$Sobrenome_autor')";
  $this->conect->insert($sql);
}

This was the only solution I could, I created a method to insert in the Connection class,and I call it in the Author class with the method cadastrar_author. I am not very happy because I believe that the connection class is only for connection, and does not add other methods. If anyone knows any other way I can get through, I’d appreciate it.