INSERT method with PHP OO error using SQL SERVER 2012

0

This is my Connection class 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 insert method:

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

    
asked by anonymous 12.04.2016 / 21:27

3 answers

1

From the code snippets of the question and the comments the main problem is that the connection is not made or correctly passed to the Autor class that performs the insert.

Other issues are:

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

  • The order of sqlsrv_query() arguments is reversed;

  • The code is vulnerable to sql injection

12.04.2016 / 22:11
1

Your code needs some remarks:

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

2) When you pass parameters to insert data in the DB, you must put the quotes, only for values (boolean, numeric, now (), null, etc ... that there is no need for quotes). However, passing the values without a prior treatment is an insecure way to register data in the database, as it gives you space for SQL Injection , you can use treatments like: preg_split() , preg_replace('/sua_expressao_regular/','',$data) , and other "more 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 .

Resolving the issue, if you are using $this , which belongs to your class in question, it does not make sense to pass the data by parameter. I understand that you should already have attributed a set method to the data "name" and "last name", since they are attributes of your class, but I would give an improvement in it, not using Capital letter at the beginning of attributes and would follow the

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, the correct thing would be 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));
    }
}

And it would also separate the connection class from your "Author" entity.

    
13.04.2016 / 17:32
-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 got, I created an insert method in the Connection class, and I call it in the Author class with the register_address method. I'm not too happy because I believe that the connection class is just for connection, and not add other methods. If anyone knows of any other way, thank you.

    
12.04.2016 / 21:28